Convert text to number

[Dal]

Hi all,

when I copy and paste data to my worksheet, the numbers appear to be
formatted as text. I can easily convert these to numbers by either selecting
the error message and converting or using the special paste method and so on.
However, my spreadsheet requires for data to be copied and pasted
continuously and I dont want to have to convert the text to numbers every
time. Is there a way this could be automated with some code?

 

[Gary''s Student]

Sub numerify()
For Each r In ActiveSheet.UsedRange
If r.NumberFormat = "@" And IsNumeric(r.Value) Then
r.NumberFormat = "General"
r.Value = r.Value
End If
Next
End Sub

 

[Kristen]

I have the problem as the original posted. I have copied the code exactly as
written. Nothing happens when I execute the macro. Am I dense? Does "r"
need to be defined. I tried selecting a range and then run the macro and it
did not work that way either.

I just checked the, cells are already formatted as general. I need to
convert to "Convert to Number". I can do this when I click on the
exclamation point but I want to incorporate this within all of the other
stuff I have written.

 

[Gord Dibben]

Kristen

I find the macro will not work if the "numbers" are preceded by an
apostrophe or contain extraneous <space(s)> and not just formatted as Text.

You could Dim r as Range but that won't help if the case is as I state
above.

Without a macro, Format all to General.

Copy an empty cell.

Select the range to change and Edit>Paste Special>Add>OK>Esc.


Gord Dibben MS Excel MVP

 

[frenchtoast]

Hi Gord,

I have a similar issue. The "number" is preceded by an apostrophe. I am
using VLOOKUP of a number.

ie. have existing '12345
need to lookup 12345 to return another value.

I tried the Paste Special function though nothing happened.

Thanks for your help.