VBA to convert to number

[steelervince]

I have a string of imported data which comes into an existing spreadsheet. It cannot be changed prior to importing as the data is provided by an external vendor.

When the data is imported into my spreadsheet the Error Checking tag comes on, after I select the cells where certain numeric data has been entered, stating that this is a range of Numbers Stored as Text. If I click on the Error Checking tag and select the Convert to Number option and then enter my VLOOKUP function I get the desired answer. If I do not convert the text to numbers then my VLOOKUP function does not work at all. The cell just displays the function text itself instead of the desired answer.

Here is what I am requesting:

Assistance with writing the VBA code to accomplish the manual task of converting the text to numbers. I have tried to format using the Ribbon without success.

Any ideas out there?

 

[steelervince]

Being new to VBA or at least learning as I go, my job has many hats to it so I do not get to spend much time sitting down and really study why VBA code does what it does.

That being said I have a general understanding of some of the commands and can sometimes adjust code to get the desired outcome.

I am not sure that answers your question, but thank you for your assistance!

 

[chazw]

Another possibility

Sub nfmt()
With ActiveSheet.UsedRange
    .Value = Evaluate(.Address & "*1")
End With
End Sub

Fascinating.

I'm trying to find a smooth way of "converting to numbers" also. I googled and found this...then I googled this and can find no meaningful info (lots of sketchy stuff, tho) on this function.

Can you elaborate on what's going on here?

 

[VoG]

Basically it is multiplying the text values by one to coerce them into numbers. Why that worked in this case and .Value=.Value didn't I have no idea

 

[chazw]

Try

Sub nfmt()
With ActiveSheet.UsedRange
    .Value = .Value
End With
End Sub

Funny -- for me, this is the code that did the trick.

The second version put #ERROR into every cell.

Basically it is multiplying the text values by one to coerce them into numbers

Gosh what a cool trick. I didn't know you could do that.

 

[TFH]

Both worked for me. However,

.Value = .Value is better because Real "text" data produces an error with

.Value = Evaluate(.Address & "*1")

For me anyway.

My Thanks Also to Peter.

 

[TheBigEasy]

Been looking for this for a while and this has given me the answer so thanks.
This was the code that did it

Sub nfmt()
With ActiveSheet.UsedRange
.NumberFormat = "General"
.Value = .Value
End With
End Sub

 

[kumthekarg]

Yeah! The code worked for me as well.....just that it removes ALL the formulas from the current sheet and converts them into number.

 

[Rick Rothstein]

Yeah! The code worked for me as well.....just that it removes ALL the formulas from the current sheet and converts them into number.

Where are your text numbers located at? The code can be modified to restrict its actions to a specified range as opposed to the entire sheet. For example...

With Columns("C")
  .Cells.NumberFormat = "General"
  .Value = .Value
End With

Just change the Columns argument to the column or columns letter range reference, or use Range(arg) where arg is as specific cell or cells reference, depending on your actual setup.

 

[kumthekarg]

Hi Rick,

Thank you very much.
My data would be pulled from one of the tools my org uses and exports data in several columns. Current macro helps me as there will be no formulas in the report extracted.

Suggestions are noted! Will modify the macro accordingly.

Regards,
Ganesh K

 

[purinqui]

In order to optimized and to another vision for anyone that couldn't fixed the famous error " Number Stored as Text"

Sub converttonumber()
'Convert numbers (stored as text) to number in a range, in this example in Range A1 to last cell with data

Dim I As Range
For Each I In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
I.Value = I.Value * 1
Next I
End Sub