CODEWITHSUNDEEP
excelnumbers
Gokul Nath KP
Gokul Nath KP

Reputation: 15553

How to save a number as a String in Excel?

How to save a number as a String in Excel?

When I try to enter a number, 00112233, Excel automatically formats it as 112233 and saving it as number. But I want the preceeding 0's not to be truncated and save the number as string.

As a workaround I'm using quotes ("") to save the actual string.

Any suggestion...??

Upvotes: 4

Views: 17126

Answers (3)

barry houdini
barry houdini

Reputation: 46341

If you pre-format the input cells with TEXT format you can just enter 00112233 or whatever and it will display as entered and be stored as text. To do that select a cell or range of cells and right click - in dialog box choose "Format Cells" and then in the next box choose Number > Text

Note you can't change to TEXT format after input, or rather you can change the format but it has no effect!

Upvotes: 5

Jerry
Jerry

Reputation: 71538

You need to use single quotes to keep it as text:

'00112233

Will store 00112233 in the cell.

Otherwise, if you have many cells already in number format, and you need them to be in text format with 8 digits (including zeros), you can use the formula on the data:

=text(A1,"00000000")

(there are 8 zeros there)

Then, copy the column containing the formula, paste in place as values (Paste Special > Values) and delete the previous column.

Upvotes: 2

JMK
JMK

Reputation: 28059

If you put a single quote in front of the number, it will be stored as text.

Upvotes: 15

Related Questions