Excel: Auto converting text to numbers

[Avi]

I am downloading an Excel sheet, and the numbers come in as text. It
basically comes in as "33 %" but Excel registers this as text, not a
percentage.

I have a cell that will be used to add the numbers, but since they are
text it doesn't work.

Given this information, is there a way to convert the imported data
into numbers. I would prefer to include this into my formula. The
potential numbers are:

0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A

I would prefer a function, again if possible, that could convert any
number. Please note, the space between the number and the % sign is
there on purpose.

 

[Dave Peterson]

You could try this:

Select an empty cell
Edit|copy

Select the range of "numbers" to be fixed
Edit|paste special|values
check Add

When I did this, my "75 %" cell changed to .75. That's still the same value,
but it lost its format.

If you're getting this data from a web page, you may have non-breaking spaces
(HTML stuff) in those cells.

You may want to try David McRitchie's routine to clean that stuff up:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm