CODEWITHSUNDEEP
excel
Amirul Islam
Amirul Islam

Reputation: 457

How to format numbers to be stored as text in excel?

In excel I have many cells of number in a column formatted as text. I want to convert them into a formatting "number stored as text". I can do it for a single cell at a time by putting an apostrophe (') before the number and pressing enter. My problem is how to achieve this type of formatting for rest of the cell quickly. I have tried to create a new column as ="'"&A2 and copy, but it does not work.

In the attached figure I have converted cell A2 as number formatted as text by putting an ' before it and want to do the same for rest of the cell quickly.number formatted as text with an apostrophe

Upvotes: 3

Views: 4340

Answers (2)

Dirk Reichel
Dirk Reichel

Reputation: 7979

While it may not be the fastest solution, just select the cells you want to be text and set the formatting to text => hit alt + F11

In the "Immediate" window run:

for each x in selection.cells: x.value = format(x.text,"'@"): next

Upvotes: 3

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

You can do this with a helper column. You'll need to know what the numberformat was before you formatted the column as text. Then just use that same format in this formula:

=TEXT(A1,"number_format")

eg:

=TEXT(A1,"General")

or

=TEXT(A1,"0")

Fill down as far as necessary.

Next, select the cells in the column with the formula and Edit-Copy; Edit-Paste Special Values This will replace the formula with the text result.

Upvotes: 1

Related Questions