How to replace with leading zeros in Excel?

In an Excel file, I need to replace, say, "foo" by "007".

I selected all the file and I explicitly put the cell format to be in Text mode.

So, why it replaces me "foo" by "7" and not by "007" as I said it :( ?

Serge

Posted 2017-09-18T12:53:58.540

Reputation: 406

what are the exact steps you've tried to do it? – Máté Juhász – 2017-09-18T12:55:03.770

open the excel file, select a column, then Ctrl+H, from "foo" to "007" – Serge – 2017-09-18T12:56:13.917

Answers

Apparently, excel interprets "007" as number when replacing. So you could use '007 in the replace-dialog, so it shows your wanted result.

IQV

Posted 2017-09-18T12:53:58.540

Reputation: 660

what does mean '007? I mean, I need just 007.. I put the format as Text, why should it treat as number? – Serge – 2017-09-18T13:01:44.733

12The ' interprets the number as text. You don't see it in your cell. – IQV – 2017-09-18T13:02:18.440

Just put a single quote in front of your leading number - ex. '01234 It will take the number as-is literally and the quote will not show in the field.

Michael Routon

Posted 2017-09-18T12:53:58.540

Reputation: 11

If you go to the cell, look at controls at top of page, choose cell format. Scroll until you see "numbers" a better choice than text.. If the choice is close to what you want you can use the examples to make your own. Once you find the correct expression it will give you "007" or any number you type to the left side of the expression but it must be seen on the far left of the cell. If you want all the numbers below that cell to have the same properties, select "column" instead of "cell" You will be presented with the same numerical formatting choices.Just change the "XXX" to "007" then the rest of the cells also will show "007, "then whatever you put in as numbers. If you want to add text why not just start another column next to the one that you just made and set the text cells as you want. You can set the text cells to show or hide a lengthy explanation of the number entered in the cell next to it. Just suggestions,Goodluck. There are small differences in versions of Excel but do not worry, just go to the help files. Then try support links. Then if you learn something help someone else ;)

fumblin along

Posted 2017-09-18T12:53:58.540

Reputation: 11

Replace with =text(7;"000") (yes, including the quotation marks)
Replace with 7, select the cells or the whole column. Go to cell formatting > Custom > type in 000
Replace with '007

Jot Sat

Posted 2017-09-18T12:53:58.540

Reputation: 1