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

by Allen Wyatt
(last updated November 1, 2017)

4

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

Changing Label Printing Order

If you want to change the order in which labels are printed when doing a mail merge, Word doesn't provide many options. ...

Discover More

Deleting Duplicate Columns

Got a worksheet in which there may be entire columns that are duplicates of each other? If you want to delete those ...

Discover More

Finding Circular References

If you have circular references in a workbook, you may see an error message appear when you first open that workbook. If ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Adjusting Values with Formulas

Paste Special is a great tool that allows you to modify the values in a range of cells in your worksheets. You may want, ...

Discover More

Understanding Variables in VBA Macros

You can create and use all sorts of variables in your macros. This tip examines all the different data types you can specify.

Discover More

Generating a List of Macros

Got a workbook that has lots and lots of macros associated with it? Here's a way you can get a list of all of those ...

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}] 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 seven more than 9?

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.