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 February 2, 2022)

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

Putting Bold Words in an Index

There are several ways you can create an index in Word, but the first step is always to figure out what should go in the ...

Discover More

Inserting Special Spaces

Do you need to frequently add en spaces and em spaces to your documents? You can add special tools to Word that make ...

Discover More

Intelligent Title Case

A common editorial need is to change the capitalization used on different words in a selection of text. Word provides a ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Sheets for Months

One common type of workbook used in offices is one that contains a single worksheet for each month of the year. If you ...

Discover More

Converting Phone Numbers

Sometimes you receive a phone number that contains alphabetic characters and you need to convert it to a purely numeric ...

Discover More

Displaying Worksheets in a Slideshow Fashion

Want to step through the worksheets in a workbook, displaying them like a slideshow? The macros provided in this tip can ...

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 four less than 6?

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.