Sometimes I get a spreadsheet with a list of numbers in text format.
The fastest way I found to convert them into numbers was to select them and then run this little bit of VBA code, typed directly into the Immediate Window:
selection.value = selection.value
Before:
After:
Of course, if your numbers have leading zeroes, you'll need to make other arrangements. What do you do?
About JP
17 Comments:
Doesn't work for me in Excel 2003/2007 – I often have this issue (numbers and dates formatted as text) and it takes a bit of messing around to get it right… Would love a quick fix such as this – what am I doing wrong?
I had to fix this for someone this week. I multiplied the numbers by 1. Your way is easier though. Good tip
I usually do a text to columns on the offending data.. works like a charm.
Hi,
Sometimes numbers importet in excel come out with a ' before. Then I use the following macro to remove the apostrophe and make it available in excel as a number.
Sub ExcelApostrophekesVerwijderen()
Dim myRng As Range
Dim myCell As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "No constants in selection!"
Exit Sub
End If
For Each myCell In myRng.Cells
If myCell.PrefixCharacter "" Then
myCell.Value = "" & myCell.Text
End If
Next myCell
End Sub
Please forgive me but i've forgotten where i've found the code. Dont sue me.
Easy — type 1 in a cell, copy it, then select your range of text and "paste special" "multiply" and hey presto you have numbers.
At this stage I have written a macro that does this and added it to a custom ribbon for ease of use.
I also use the paste special -> multiply option
Instead of Paste Special > Multiply by one, use Paste Special > Add zero. You can use a blank cell for zero, so you don't need to pollute and later clean up a blank cell.
Even better, for one-column ranges, is Text-To-Columns, using fixed width without any splits.
@Jon,
I like the PasteSpecial method over the Text-To-Columns method because it will handle cells Formatted as Text that contain a number also.
Rick -
Good point. Both approaches use the formatting of the target cell, but pasting applies the formatting of the copied cell as it performs the operation. Try using paste-special-values with the operation: the cells will remain text.
Using the Text to Column feature is the easiest way for me.
Excellent tip JP…
I use text to column if the number is part of larger data set. Otherwise, use the paste special. But I like your one line code too.. will come handy when dealing with text thru VBA…
@Chandoo… What "one line code"? I don't see any "one line code" in this thread… did I miss something?
I think he means the "selection.value = selection.value" in the blog article.
Doh! But of course.
There are many ways; most mentioned above. Another way would be to use =value() function
But that leaves you with the numbers in a different location..
In case you want it at the same location, then copy 1 typed in a different cell and paste special values n operator multiply onto these text cells.
Hey
I want to convert Numerics including decimails in 1 cell of excel to text in another cell. I need a formula in excel that can do this for me.
For eg
Cell A1 = 235
If i enter the formula in cell A2 it should output: "Two Hundred and Thirty Five" or Two Hundred Thirty Five"
@Kartik
Follow this link http://support.microsoft.com/kb/213360