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 :( ?

enter image description here

migrated from serverfault.com Sep 18 '17 at 12:54

This question came from our site for system and network administrators.

  • what are the exact steps you've tried to do it? – Máté Juhász Sep 18 '17 at 12:55
  • open the excel file, select a column, then Ctrl+H, from "foo" to "007" – Serge Sep 18 '17 at 12:56

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

  • what does mean '007? I mean, I need just 007.. I put the format as Text, why should it treat as number? – Serge Sep 18 '17 at 13:01
  • 12
    The ' interprets the number as text. You don't see it in your cell. – IQV Sep 18 '17 at 13:02

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 ;)

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.