Number Text Format

E

evoxfan

I have a column of numbers formatted as text such as:
099.600
'101.550
102.000
'104.430
105.500

Some numbers have an apostrophe in front and some do not.

I would like to make all the numbers in this column consistent without the
apostrophe. I would also like to keep them as text since converting them to
numbers would likely get rid of the first character for numbers less than 100.

What is the best way to accomplish this?

Thanks in advance for your help.
 
J

Jonathan Cooper

You could create a helper column. Use the formula =TEXT(A2,"000.000").
copy/paste the formula next to all of your data, then copy and paste the
values of the formulas.
 
R

Rick Rothstein

First, select all of the cells with these numbers in them, then format your
cells as text, then press Alt+F11 and execute this statement in the
Immediate window (press Alt+G if you don't see the Immediate window)...

Selection.Value = Selection.Value
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel does not accept text format! 0
How can the font and size of (all) citation numbers be altered? 1
Inconsistent formatting of section numbers 0
How to count the numbers in a Word document? 0
Creating Different Headers in Word 0
Centered text that does not stay centered 0
Template with fields and premade texts.. 0
Reading CSV with numbers in text format, Excel automatically convertsto scientific notation 1

Top