convert number to text without having to click on every cell

I have numbers in a file that show as 2.01001E+17 -- if I convert the column to text, it doesn't actually do anything until I double click a cell and press enter -- then it actually shows the value in the cell.

The problem is -- I need to save as tab delimited -- and excel, as stupid as it is, decided it is a better idea to put what is displays in the cell, rather than what is actually in there.  (ok for formulas -- not OK when it is showing reduced versions of numbers)

There are 10K+ cells I need to convert to text -- how can I do this without having to going through every cell, 1 at a time?
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

XetroximynConnect With a Mentor Author Commented:
Thanks for all the ideas -- got me thinking -- what I did was made a new column formatted as general and did the formula =CONCATENATE("",O2) -- which showed the number with a preceding ' -- then I formatted the new column as text and replaced all ' with blank.

Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Format them as text
TracyConnect With a Mentor VBA DeveloperCommented:
Select the cells, right click --> Format --> on the General tab click Number, then click OK
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Saurabh Singh TeotiaConnect With a Mentor Commented:
Do the following steps...
  1. Format the cells as number from text for the range you want to convert
  2. Then write a 1 in a blank cell and copy it.
  3. Go to your numbers what you want to convert and select them and right click-->paste special-->paste-->values-->operation-->multipy and hit ok.
  4. There you go all your data will be converted to number.
Alternatively once you select your data you can also run the following macro.

Sub convertdata
dim rng as range

Set rng=Application.selection


End Sub

Open in new window

Saqib Husain, SyedConnect With a Mentor EngineerCommented:
or select the cells and run this sub

Sub cvt2txt()
For Each cel In Selection
cel.Value = "'" & cel.Value
Next cel
End Sub
Saurabh Singh TeotiaConnect With a Mentor Commented:
Ignore my comment i thought you want to change them to number from text, However you can use the following macro to change them to text from number...
Sub convertdata()
Dim rng As Range, cell As Range

Set rng = Application.Selection
  rng.NumberFormat = "@"
For Each cell In rng
cell.Value = "'" & cell.Value
Next cell
End Sub

Open in new window

Anthony MellorConnect With a Mentor Chartered AccountantCommented:
changing the style is another formatting solution option:

format / style / modify / number / select category: Number select decimal places OK

This changes the whole FILE's default, so presets the process for any new sheets, so requiring no further work for further data populations.

All Courses

From novice to tech pro — start learning today.