need to add/remove characters over thousands of cells

[Nadia]

I have thousands of payroll records that are imported into excel on a daily
basis.
The cost codes appear like this: 7002/1045.824240305
The 7002/ needs to be removed and a . (dot) inserted five from the end
like this: 1045.8242.40305
Please note there are hundreds of different cost codes so I cannot simply
copy and paste but they must all be coverted so the format is the same.
I am desperate for suggestions.
cheers,
Nadia

[Anne Troy]

Try this formula: =MID(A1,FIND("/",A1,1)+1,LEN(A1))
Then use a custom format to get the dot in there.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com



"Nadia" <[email protected]> wrote in message
news:[email protected]...
> I have thousands of payroll records that are imported into excel on a
daily
> basis.
> The cost codes appear like this: 7002/1045.824240305
> The 7002/ needs to be removed and a . (dot) inserted five from the end
> like this: 1045.8242.40305
> Please note there are hundreds of different cost codes so I cannot simply
> copy and paste but they must all be coverted so the format is the same.
> I am desperate for suggestions.
> cheers,
> Nadia

[Dave Peterson]

Another option:

=REPLACE(REPLACE(A1,LEN(A1)-4,,"."),1,5,"")

If those prefix characters to be removed are always 5 characters.

Nadia wrote:
>
> I have thousands of payroll records that are imported into excel on a daily
> basis.
> The cost codes appear like this: 7002/1045.824240305
> The 7002/ needs to be removed and a . (dot) inserted five from the end
> like this: 1045.8242.40305
> Please note there are hundreds of different cost codes so I cannot simply
> copy and paste but they must all be coverted so the format is the same.
> I am desperate for suggestions.
> cheers,
> Nadia

--

Dave Peterson

[Nadia]

Thank you both so very much for taking the time to reply.
Dave yours worked a treat!
cheers,
Nadia

"Dave Peterson" wrote:

> Another option:
>
> =REPLACE(REPLACE(A1,LEN(A1)-4,,"."),1,5,"")
>
> If those prefix characters to be removed are always 5 characters.
>
> Nadia wrote:
> >
> > I have thousands of payroll records that are imported into excel on a daily
> > basis.
> > The cost codes appear like this: 7002/1045.824240305
> > The 7002/ needs to be removed and a . (dot) inserted five from the end
> > like this: 1045.8242.40305
> > Please note there are hundreds of different cost codes so I cannot simply
> > copy and paste but they must all be coverted so the format is the same.
> > I am desperate for suggestions.
> > cheers,
> > Nadia
>
> --
>
> Dave Peterson
>