need to add/remove characters over thousands of cells
 

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

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" wrote in message
...
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

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

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