Alter Account numbers

I have a list of approx 9000 account numbers that are in the format:

#####-###-### (ex: 35000-455-876)

I would like to add "03-" to the front of the numbers and delete the last
"-###", so the final format would be:
03-#####-### (ex: 03-35000-455)

Any suggestions? I can't seem to figure out the concantenate function.

Thanks!
Ann

Ann,


Assume data is in A2 then in b2 put:

="03-" & LEFT(A2,9)

Change cells(Columns) as needed and copy this formula as required.

HTH

"Ann" wrote:

> I have a list of approx 9000 account numbers that are in the format:
>
> #####-###-### (ex: 35000-455-876)
>
> I would like to add "03-" to the front of the numbers and delete the last
> "-###", so the final format would be:
> 03-#####-### (ex: 03-35000-455)
>
> Any suggestions? I can't seem to figure out the concantenate function.
>
> Thanks!
> Ann

or a macro to do all
Sub fixnumbers()
'="03-" & LEFT(A2,9)
For Each c In Selection
c.Value = "03-" & Left(c, 9)
Next c
End Sub

--
Don Guillett
SalesAid Software
donaldb@281.com
"Toppers" <Toppers@discussions.microsoft.com> wrote in message
news:061208E0-5913-4104-8E55-7018F9131E40@microsoft.com...
> Ann,
>
>
> Assume data is in A2 then in b2 put:
>
> ="03-" & LEFT(A2,9)
>
> Change cells(Columns) as needed and copy this formula as required.
>
> HTH
>
> "Ann" wrote:
>
>> I have a list of approx 9000 account numbers that are in the format:
>>
>> #####-###-### (ex: 35000-455-876)
>>
>> I would like to add "03-" to the front of the numbers and delete the last
>> "-###", so the final format would be:
>> 03-#####-### (ex: 03-35000-455)
>>
>> Any suggestions? I can't seem to figure out the concantenate function.
>>
>> Thanks!
>> Ann

Thanks! That was so incredibly easy! I was trying to make to much work of it.

Thanks,
Ann

"Toppers" wrote:

> Ann,
>
>
> Assume data is in A2 then in b2 put:
>
> ="03-" & LEFT(A2,9)
>
> Change cells(Columns) as needed and copy this formula as required.
>
> HTH
>
> "Ann" wrote:
>
> > I have a list of approx 9000 account numbers that are in the format:
> >
> > #####-###-### (ex: 35000-455-876)
> >
> > I would like to add "03-" to the front of the numbers and delete the last
> > "-###", so the final format would be:
> > 03-#####-### (ex: 03-35000-455)
> >
> > Any suggestions? I can't seem to figure out the concantenate function.
> >
> > Thanks!
> > Ann