Search and replace with a number that starts with zeros

Hello,

I have a large list of numbers on tools.
Some of the tools numbers start with zeros.
When I have exported the list to Excel all the numbers in the beginning is missing.
Because of that I want to create a macro that searches for these numbers and replaces it with the correct one.
For exampel if the number 22 occurs in column D and/or E it would be replaced with the number 0022.

I have attached an exampel bok.

Thanx!
/Obelix

Re: Search and replace with a number that starts with zeros

Quote from royUK

Why not just use a Custom number format? In your example, select Column A. Then Format >Cells > Custom & use 0000 > OK.

That won't work because I'm gona use those numbers in another program and when I copy those cells the zeros won't follow because they aren't in the cell.

/Obelix

Re: Search and replace with a number that starts with zeros

Quote from Dave Hawley

Format the cells as Text then.

I still need to get the zeros in there because they aren't there from start.

/Obelix

Re: Search and replace with a number that starts with zeros

Quote from Dave Hawley

use ="00"&A1 then change to values only.

That I had in the beginning was just an exampel.
Some have only 1 zero and some have 3. Some numbers are total 4 digits and some are 5. And as seen in the exampel bok som aren't just numbers.
So that will not work ither.

/Obelix

Re: Search and replace with a number that starts with zeros

Quote from Dave Hawley

Last try as you are only giving a bit more relavant details after each attempt to help you. Use

=IF(ISNUMBER(A1),CHOOSE(LEN(A1),"000"&A1,"00"&A1,"00"&A1,"0"&A1),A1)

Ok, I'll try explain again. I want a macro that searches for several specific cell contents and replaces it.

For exampel it searches for all cells with a 9 and replaces those with 0009 and then continues searches for all cells with a 22 and replaces those with 0022 and then continues with searching for all cells with a 419-00 and replaces those with 00419-00 etc etc etc

I'm sorry but I'm not good at explaining, especially in english.

/Obelix