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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Format them as text
TracyVBA DeveloperCommented:
Select the cells, right click --> Format --> on the General tab click Number, then click OK
Saurabh Singh TeotiaCommented:
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

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Saqib Husain, SyedEngineerCommented:
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 TeotiaCommented:
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 MellorChartered 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.

XetroximynAuthor 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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.