create simple macro

noraisens · #1 02-11-2006, 02:10 PM

I have one column of 500 numbers similar to this "cc03052" they all start with cc I need the macro to add "05" into the number at a specific point then to go the next.

before after
cc03052 cc0305052
cc04130 cc0412050

can anyone help me

noraisens · #2 02-11-2006, 02:13 PM

[quote=noraisens]I have one column of 500 numbers similar to this "cc03052" they all start with cc I need the macro to add "05" into the number at a specific point then to go the next.

before after
cc03052 cc0305052
cc04130 cc0412050

can anyone help me
thanks,
Ron

#3 02-11-2006, 02:40 PM

You need to be a bit more specific. There doesn't seem to be a pattern here.

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"noraisens" <noraisens.232x6y_1139685303.8615@excelforum-nospam.com> wrote
in message news:noraisens.232x6y_1139685303.8615@excelforum-nospam.com...
>
> I have one column of 500 numbers similar to this "cc03052" they all
> start with cc I need the macro to add "05" into the number at a
> specific point then to go the next.
>
> before after
> cc03052 cc0305052
> cc04130 cc0412050
>
> can anyone help me
>
>
> --
> noraisens
> ------------------------------------------------------------------------
> noraisens's Profile:
> http://www.excelforum.com/member.php...o&userid=31450
> View this thread: http://www.excelforum.com/showthread...hreadid=511382
>

#4 02-11-2006, 02:45 PM

Can you not use a formula?

If the old values are in column A in B1 enter

=LEFT(A1,6)&"05"&RIGHT(A1,1)

And copy down. This presumes they (the old value) are all the same length.
If not paste back

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS

"noraisens" <noraisens.232x6y_1139685303.8615@excelforum-nospam.com> wrote
in message news:noraisens.232x6y_1139685303.8615@excelforum-nospam.com...
>
> I have one column of 500 numbers similar to this "cc03052" they all
> start with cc I need the macro to add "05" into the number at a
> specific point then to go the next.
>
> before after
> cc03052 cc0305052
> cc04130 cc0412050
>
> can anyone help me
>
>
> --
> noraisens
> ------------------------------------------------------------------------
> noraisens's Profile:
> http://www.excelforum.com/member.php...o&userid=31450
> View this thread: http://www.excelforum.com/showthread...hreadid=511382
>

#5 02-11-2006, 02:50 PM

Don

I took it the second one was a typo

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS

"Don Guillett" <dguillett1@austin.rr.com> wrote in message
news:OAMoNK0LGHA.2916@tk2msftngp13.phx.gbl...
> You need to be a bit more specific. There doesn't seem to be a pattern
> here.
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "noraisens" <noraisens.232x6y_1139685303.8615@excelforum-nospam.com> wrote
> in message news:noraisens.232x6y_1139685303.8615@excelforum-nospam.com...
>>
>> I have one column of 500 numbers similar to this "cc03052" they all
>> start with cc I need the macro to add "05" into the number at a
>> specific point then to go the next.
>>
>> before after
>> cc03052 cc0305052
>> cc04130 cc0412050
>>
>> can anyone help me
>>
>>
>> --
>> noraisens
>> ------------------------------------------------------------------------
>> noraisens's Profile:
>> http://www.excelforum.com/member.php...o&userid=31450
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=511382
>>
>
>

noraisens · #6 02-12-2006, 08:03 PM

Yes, the second post was a mistake.

I have 6 columns with information.
the second column has an id number with 2 letters and 5 numbers (ie: ccxxxxx). example: cc14081.
I would like to make this id number bigger by adding 2 numbers in a specific place in the id number (ie: ccxxxx05x). example: cc1408051.

I took the column of numbers and placed it in a new worksheet in column a

i took the formula =LEFT(A1,6)&"05"&RIGHT(A1,1) and placed it in column b.

I then copied it down and It worked. Thank you.

can I recap what was done.

LEFT(A1,6) in cell A1 go from the left 6 spaces
&"05"& insert 05
RIGHT(A1,1) in cell A1 go from the right 1 space and leave the digit in place.

Is this correct?

Is it possible to make this so that you can actually take the number out and replace it. as long as the column was identified?

thanks,
Ron

#7 02-13-2006, 03:50 PM

Ron

Left is taking the data from left of an input cell (A1) and then taking the
first 'x' characters. Right doe the same from the right. The other one you
have that could certainly be used to replace the number is MID

=MID(CellRef,StartCharacter,NoCharactersToTakeFromStartCharacter)

If that makes sense

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS

"noraisens" <noraisens.2358an_1139793001.3205@excelforum-nospam.com> wrote
in message news:noraisens.2358an_1139793001.3205@excelforum-nospam.com...
>
> Yes, the second post was a mistake.
>
> I have 6 columns with information.
> the second column has an id number with 2 letters and 5 numbers (ie:
> ccxxxxx). example: cc14081.
> I would like to make this id number bigger by adding 2 numbers in a
> specific place in the id number (ie: ccxxxx05x). example: cc1408051.
>
> I took the column of numbers and placed it in a new worksheet in column
> a
>
> i took the formula =LEFT(A1,6)&"05"&RIGHT(A1,1) and placed it in column
> b.
>
> I then copied it down and It worked. Thank you.
>
> can I recap what was done.
>
> LEFT(A1,6) in cell A1 go from the left 6 spaces
> &"05"& insert 05
> RIGHT(A1,1) in cell A1 go from the right 1 space and leave the digit in
> place.
>
> Is this correct?
>
> Is it possible to make this so that you can actually take the number
> out and replace it. as long as the column was identified?
>
> thanks,
> Ron
>
>
> --
> noraisens
> ------------------------------------------------------------------------
> noraisens's Profile:
> http://www.excelforum.com/member.php...o&userid=31450
> View this thread: http://www.excelforum.com/showthread...hreadid=511382
>

Welcome to the Excel Forum

Forum Statistics