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

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

Determining Word Versions of Documents

If you know where to look, you can figure out which version of Word was last used to work on a document. It takes some ...

Discover More

Random Width and Height Changes

Have you ever been using a workbook, only to open it one day and find that Excel has changed the height of your rows or ...

Discover More

Stopping the Deletion of Cells

You can delete cells from a worksheet, and Excel will move the remaining cells either to the left or upwards. Deletions, ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Pausing Macros for User Input

Does your macro need to get some input from a user? Here are the ways that Excel provides for that input to be solicited.

Discover More

Deleting Every X Rows

Grab some info from a source other than Excel, and you may find the need to delete a certain pattern of rows from a ...

Discover More

Checking for the Existence of a File

The data stored in a worksheet can often correspond to information external to that worksheet. For instance, you might ...

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 8 + 0?

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.