Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Nadia
 
Posts: n/a
Default 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   Report Post  
Anne Troy
 
Posts: n/a
Default

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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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   Report Post  
Nadia
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
remove non-numeric characters from a cell SWBodager Excel Discussion (Misc queries) 14 December 16th 05 08:49 PM
Deleting Blank Characters in a Cell PokerZan Excel Discussion (Misc queries) 4 June 3rd 05 09:43 PM
Deleting 3 Text characters from the right Helen Excel Worksheet Functions 7 April 26th 05 04:17 PM
How do you make some characters in a cell bold and some not? tracman Excel Discussion (Misc queries) 4 March 28th 05 05:17 AM
Characters and Formulas Bonny Excel Discussion (Misc queries) 1 February 11th 05 05:06 PM


All times are GMT +1. The time now is 04:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"