Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove non-numeric characters from a cell | Excel Discussion (Misc queries) | |||
Deleting Blank Characters in a Cell | Excel Discussion (Misc queries) | |||
Deleting 3 Text characters from the right | Excel Worksheet Functions | |||
How do you make some characters in a cell bold and some not? | Excel Discussion (Misc queries) | |||
Characters and Formulas | Excel Discussion (Misc queries) |