Free Microsoft Excel 2013 Quick Reference

VBA convert to number

Hi all

I have a spreadsheet which has extracted values from another program. They values are returned as text i.e. '100 as opposed to a number which would be 100. Also nil values are returned as '— instead of a 0.

I can convert the '100 type text to numbers in vba but cant figure out how to convert the '— values.

I have attached my workbook which has the code for converting numbers. You will see it crashes when it hits a '—.

Many thanks for your help in advance.


Post your answer or comment

comments powered by Disqus
After importing my report to Excel my numbers are listed in text format with the green arrow in the upper corner (see attached worksheet). For my situation, the problem can easily be fixed by highlighting all of the fields and selecting "Convert to Number" from the exclamation point drop down.

Is there a way using VBA Code to replicate this process? I would like all of my values to retain their format (percentage, dollar, etc). but no longer have the green notices in the upper corner.

Any help would be greatly appreciated. Thanks!

Short story: Is there a macro command that will "Convert to number" a selection before I paste it?

Long story: I have a macro to get info from another excel file. The reference file has "numbers" and "numbers stored as text". I am using this info with a vlookup command in my file. My cells are "numbers". The vlookup won't work when the reference file info is "numbers stored as text". I could go into the info and edit each cell when this occurs, but that's too time consuming.

I can not change the "reference file"! It's poorly formatted, but that's another departments problem.

Any help is appreciated.



I have a very large database which has been exported to excel. When the data is in excel it has all been formated with a ' before all the numbers and text
'Customer ABC

This means that the numbers are stored as text.

Is there any way in which I can quickly (maybe VBA) lose all instances of this ' before each cell contents and that if it appears as a number it is then converted to a number so my lookups will work.

I can select them all manaully and select convert to number but I would rather another way as i have to do this on a number of files.


when i import external data using "import data" some of the number display as text, and there is an error beside it. i tried to change the format to number but it has no change, however if i tried to expand the error message and use the "convert to number" option it will then change to number

so the question will be, can i create a VB(may be) so that i can turn the text to number?

thank you

ok, im making an index of ID numbers..they're not big they're literally 1-20 lol the spread sheet looks lke this
in cell a1 "WELDER ID" in cell a2 "1" and so on..but there are multiple 1s in a row..and 2's..which is what i want..
the issue im running into is, sometimes i get an error message when i type a number in..and in order to get rid of it i have to convert to number.. i dont understand why im getting this message because i selected all of the cells from 1-1000 we're at like 300, and even though i did that, im still getting that message.... any ideas?

When I have a lot of data that I want to use the "convert to Number", the
pop-up with the "exclamation mark inside the yellow diamond" disappears.

Essentially, I want to highlight 1 column, but 5 screens worth of data,
using SHIFT-PAGEDOWN. However, when I go past one screen, the yellow diamond
does not consistenly appears. I need this so that I can use the "convert to
number" functionality".

Any ideas on how to make this appear, or is there another way to "convert to

Thanks for any ideas!

I have a workbook in Excel 2002 that is linked to my ACCPAC (accounting)
database using an ODBC driver called F9. The workbook has 29 worksheets
which access the database to produce a set of financial statements. For some
reason, formulas will convert to numbers without warning. It's different
formulas each month. I don't know if they change when I recalculate
(calculation is set to manual) or when I save the workbook, or when I open
it, but it happens on a regular basis.

I've tried locking the cells, but then the ODBC driver doesn't work properly.

My questions a

1) Is there a way to fix this problem?

2) Is there an easy way to search through the entire workbook and find the
formulas that have converted to numbers? Currently I have to do this
manually and it can take a very long time to search through all the cells of
29 worksheets each month!

I have a coulmn of numbers that need converted to numbers. The format cell
won't work... You highlight all the numbers and the little ! box comes on and
you click convert to number. However, it does not record that function in a
macro.. I need to do this as it is part of a vlookup macro...

I have a table that has a column with "numbers" with 16 characters which I
cannot convert to number format using regular ways (if we use the formula
isnumber that will return FALSE). i tried to use MID, etc but still cannot
convert them in numbers. Is there somehow an easier way to do it?

When I have a lot of data that I want to use the "convert to Number", the
pop-up with the "exclamation mark inside the yellow diamond" disappears.

Essentially, I want to highlight 1 column, but 5 screens worth of data,
using SHIFT-PAGEDOWN. However, when I go past one screen, the yellow diamond
does not consistenly appears. I need this so that I can use the "convert to
number" functionality".

Any ideas on how to make this appear, or is there another way to "convert to

Thanks for any ideas!


I have to deal with some numbers that sometimes begin with one or more zeros.



Those are all different numbers in the system.

When using formulas this causes problems, because even if I set the cell type to "text," existing columns of numbers do not automatically convert to numbers stored as text. I literally have to double click on every single cell in the column to actually convert the cell context to text.

-I have tried =CONCATENATE("'",(A1)) , but that just returns a '012345.
-I have tried every paste special command

For this specific task I am just trying to use vlookup, so I need to have the cell formats match for it to work.

If there is another formula or way of doing this I am all ears.



I have a column formated as text that i need to convert to numbers.
Example: 0.01 (text format) into 0.01 (number format)
Any idea how this can be done?

Hi all,

I have a userform that users inout numbers into which then get placed on a spreadsheet,
now the spreadsheet is formatted to Number, but when I use the form it converts back to text and therefore does not work properly on my summary page (this page uses formulas to calculate each users productivity)

any help would be greatful.


Having read previous replies to similar questions, i'm still struggling to get the .Texttocolumns function to work.

I have several columns of numbers stored as text which i wish to convert to numbers. I am trying to use the following code. But get the error, "Run time error 1004, Method 'Range' of '_Global' failed".

Do While Column < 12
Range(Cells(Row + 1, Column), Cells(Row + 200, Column)).Select
Selection.TextToColumns Destination:=Range(Cells(Row + 1, Column)), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Column = Column + 1
Can anyone see anything i've obviously done wrong?

I have a text box with a concatentation formula. The number in the concatentation is being shown as a text. How do I convert the number to show as number within the formula instead of a text. Most likely very easy, I am just not thinking of the solution. Thx

Sorry if this has been answered before. I did a search and couldn't find anything.

I have a column of numbers which is stored as text.

I can convert them to number but need to keep all the preceding zeros eg 01823. When I convert, I lose the zero.

Is there a way around this?

Thanks in advance.


I have retrieved some data from external source and it put to my
worksheet in one column numbers in text format and if I want to work
with numbers (add, substract, etc.) I need it to convert to number format.
I tried to select column and mark it as number format however it doesn´t
work. Pls help me what to do, how to suceed. I have no chance to change
the format in source.


Hi - downloaded an Excel file that needed pivoting, but the numbers were saved as text, so the pivot table wouldn't add them up. Nothing new there, but the 3 ways in the Help section to get them back to being numbers didn't work. No error marker comes up, so I can't follow the links in that to convert to number. Formatting to number doesn't do the trick either. Now I've tried Paste Special with the Multiply option and that hasn't worked. Is there any other way to get these numbers recognised as numbers again?


I am currently trying to find an easy way to convert cell references to number format.

The issue I'm having is when compiling a small formula (CONCATENATE) as part of a macro in order to produce a unique reference number to a column of data (1000's of cells long) I come accross the age old number stored as text issue after the macro has converted the formula to values.

Have tried editing the macro to number stored as text = false which removes the annoying little red triangle but the vlookup functions still wont read the values. My current solution is to highlight the entirity of the data and convert to number which takes a long long time as there are multiple lookups assigned to the cells, which for my current project is not acceptable.

Am looking for a quick solution, (with no interaction required) something that would convert the cells to the format required either before the formula is assigned and overwritten by values or just after. Currently I'm stumped.

Any help most welcome and appreiciated!

Hello all-
I am attempting to make a macro that will make a pivot table that uses a
field that is a list of management areas where some are alphanumeric(6W,9T),
some are numeric(15,16,17). When I export the data I need from our oracle
database to excel(excel5 with headers), the whole management area field comes
up as text. The numeric data comes up with excel errors the ones with the
green dog ear in the corner. To fix the error I highlight these cells and
click the exclamation point icon that appears, and select the option to
convert text to number. That solves the problem. I want to record a macro
to accomplish this. If anyone knows how to do this. I need to know, I'm
fairly versed in VB if anyone has any code for this. Thank you!!!

when i choose tools,option,view,formula the date in date formatted cells
converted to a number, why?
i am working in excel 2003
thank you


I'm working with a spreadsheet for which I need to create a Macro.
When I copy/paste external data in this spreadsheet some of it has a green triangle with a exclamation mark. When I click that it wants me to select "convert to number".

Is there a way to include this so that the Macro runs this first prior to anything else?

Tino XXL

In excel, I tried to convert numbers to number format, using
format/cells/number from the category list. But it won't take, and stays
text-like. Any ideas?

I just went through a labourous procedure of transfering data from Notepad
into Excel (PDF documents had previously been converted to Notepad to extract
text). I used the MID function to extract the specific number I needed from
all the extranious data from the copy/paste. I now have all this wonderful
data that I need to tabulate but I realize now it is all text that looks like
numbers. I cannot perform any math functions and when I try to format the
cells to convert to numbers it does not work.

Please tell me that there is a way to convert this text to real numbers!

No luck finding an answer? You could always try Google.