Find/Replace strips out zeros

**mhlangensiepen** · 08-07-2012, 11:06 PM

Hi,
I have an Excel 2010 sheet in which I have a columns of part numbers in the form 003.04.010

I need another column where the period is stripped from this format so that the above part number would become 00304010

The relevant columns have been specified as text but when I try a find/replace to get rid of the period (find /, replace null) the part number gets the leading zeros stripped out and becomes
304010

I have tried all the parameters I can find without any luck. Anybody got any ideas?

Thanks

Mike

EDIT: Problem solved - used OpenOffice to do the find/replacement (which does it correctly - thanks a bunch Microsoft) and saved it back in Excel format.

**outofthehat** · 08-08-2012, 01:30 AM

For future reference, use =SUBSTITUTE(A2,".",""), which removes the period(s) from A2 and leaves the answer as text.

**mhlangensiepen** · 08-08-2012, 02:00 AM

Originally Posted by outofthehat

For future reference, use =SUBSTITUTE(A2,".",""), which removes the period(s) from A2 and leaves the answer as text.

Thanks for that. Be up in Nottingham later this year - my sister lectures at N/T Uni. Nice place.

**Cutter** · 08-08-2012, 09:27 AM

@ mhlangensiepen

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

**mhlangensiepen** · 08-08-2012, 07:51 PM

Thanks for this. I was not aware of that requirement (I haven't been on the forum for a long while!). I'll try & remember in future and will check any other active threads of mine now.