Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Converting Text to Numbers.

Converting Text to Numbers

Written by Allen Wyatt (last updated February 2, 2022)
This tip applies to Excel 2007, 2010, and 2013


9

If you are using Excel to grab information from an external source, it is possible that you could end up with some pretty strange information in your cells. For instance, let's say that you have cells that contain numbers such as 1,234.5-. These are formatted as text cells in Excel, and therefore cannot be used in calculations.

The following macro will check the cells in a selected range. If the cells contain text, and that text ends in a minus sign, the macro will move the minus sign to the beginning of the text and stuff it back into the cell. The result is that the cell is converted from a text value to the proper numeric value.

Sub ConvToNum()
    Dim MyText As Variant
    Dim MyRange As Range
    Dim CellCount As Integer

    Set MyRange = ActiveSheet.Range(ActiveWindow.Selection.Address)
    For CellCount = 1 To MyRange.Cells.Count
        MyText = MyRange.Cells(CellCount).Value
        If VarType(MyText) = vbString Then
            MyText = Trim(MyText)
            If Right(MyText, 1) = "-" Then
                MyText = "-" & Left(MyText, Len(MyText) - 1)
                MyRange.Cells(CellCount).Value = MyText
            End If
        End If
    Next CellCount
End Sub

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11728) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Converting Text to Numbers.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Protecting a Graphic

Need to make sure that someone cannot delete a graphic in a worksheet? The ability to protect the graphic depends on ...

Discover More

Condensing Sequential Values to a Single Row

If you have a bunch of ZIP Codes or part numbers in a list, you may want to "condense" the list so that sequential series ...

Discover More

Finding the Path to the Desktop

Figuring out where Windows places certain items (such as the user's desktop) can be a bit frustrating. Fortunately, there ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Workbook Events

You can create macros that run whenever Excel detects a certain event happening within an entire workbook. This tip ...

Discover More

Using SUM In a Macro

Want to use a worksheet function (such as SUM) from within a macro? Here's how easy it is to accomplish the task.

Discover More

Determining the RGB Value of a Color

Excel allows you to fill a cell's background with just about any color you want. If you need to determine the RGB value ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three less than 3?

2022-02-05 12:14:03

J. Woolley

At the risk of "flogging a dead horse," here is an abbreviated version of the TextToNumber macro mentioned below. The selected range is flexible because formulas and numeric or logical constants are ignored. Only cells with text constants (before conversion) remain selected. Numeric text constants (including trailing minus) are converted, as are date and/or time or True/False text constants.

Sub TextToNumber()
Dim rText As Range, C As Range, S As String
' Intersect is necessary in case Selection.Cells.Count = 1
Set rText = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants, xlTextValues))
If rText Is Nothing Then Exit Sub
rText.Select
For Each C In rText
S = C.Value
If Right(S, 1) = "-" Then
S = "-" & Left(S, (Len(S) - 1))
If IsNumeric(S) Then C.Value = S
Else
C.Value = S
End If
Next C
End Sub

See https://sites.google.com/view/MyExcelToolbox/


2022-02-04 11:48:18

J. Woolley

The VALUE and NUMBERVALUE functions do not handle a trailing minus, but My Excel Toolbox's TextToNumber macro does. It is faster than the Tip's macro for a large selected range; it also works for a discontinuous selection. The method proposed by Joan Koskela and Michael Avidan is clever and might be more convenient, but it only works for one column at a time.
See https://sites.google.com/view/MyExcelToolbox/


2022-02-04 05:31:41

Peter Atherton

Richard
COUNTIF or SUMPRODUCT should do the trick; generally =COUNTIF(original data, cell unique) or =SUMPRODUCT(--(Data rng = Unique value))

Data in A3:A10, First unique value in C3, =COUNTIF(A3:A10,C3)
or =SUMPRODUCT(--(A3:A10=C7))


2022-02-03 10:10:13

Richard Hellenbrecht

New Unique Function in Office 365
First, I love Excel Tips and read it every day.
Question: I recently used the Unique function on a table of around 900 lines of different firms and contracts. Unique identified the 600 or so unique firms with a simple =unique(b2:b900). I see that unique can be combined with various other functions (sort, sum, avg, etc.). I would love to know how many times each unique firm appeared. Tried a few things, but didn't work, so I hand counted them. Any ideas?


2022-02-02 12:36:51

Joan Koskela

I learned this technique a couple of years ago:

1. Select the cells that contain numbers reading as text.
2. Change the cell format to Number (with desired formatting).
3. On the Data tab, choose Text to Columns and click Finish.

I tested it and it does convert the number 1,234.5- to -1234.50. I even have this technique in some macros.


2013-12-23 05:39:36

Michael (Micky) Avidan

@Willy,
I must confess that when I "step over" an unnecessary Macro - I don't bother to waste my time in exploring it.
************************
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL


2013-12-22 08:53:58

Willy Vanhaelen

Micky: I entirely agree with you. If Excel can do the job then you certainly don't need a macro. But looking at the macro presented in this tip I was perplexed by its unnecessary complexity. There is a simple solution though: use the For Each ... Next statement that has been designed for these situations. I can't resist presenting a 'cleaned' version of this macro:

Sub ConvToNum()
Dim cell As Range, MyText As String
For Each cell In ActiveWindow.Selection
If VarType(cell) = vbString Then
MyText = Trim(cell)
If Right(MyText, 1) = "-" Then
cell = "-" & Left(MyText, Len(MyText) - 1)
End If
End If
Next cell
End Sub


2013-12-21 11:56:16

Michael (Micky) Avidan

To the best of my knowledge, there is no need for any macro to achieve that task - even if the MINUS sign was typed as the right most character.
Selecting the range of cells and applying 'Data' > 'Text to Columns' > 'Next' > 'Next' > 'Finish' - will do the job by changung the MINUS sign location.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL


2013-09-23 08:22:34

Bryan

This tip should be titled "converting numbers with a minus sign at the end to numbers with a minus sign at the beginning".

If you have normal numbers stored as text (for instance, by preceding with an apostrophe, or by selecting "text" as the number formatting), there are two ways to convert them back to numbers:

1) If you have the right error-checking rule in place (XL2007: Office button > Formulas > Error checking rules > Numbers formatted as text or preceded by an apostrophe), you should have the green formula error triangle appear in the corner of the cell(s) containing text numbers. Once you select the cell(s) a diamond-shaped flag appears -- click it, then select "convert to number".

2) If your text numbers are appearing as part of a macro, or if you want more control over how the numbers are converted, you can set the .Value property of the cells equal to itself. For instance, if the cells to change are in a Range variable called rng, the following line will convert them to a number:

<code>rng.Value = rng.Value</code>

I use the second approach because I get sheets that are formatted as text in order to keep the decimals aligned. I have a routine that counts the number of decimals then after converting to numbers, sets the number formatting to match what was there before.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.