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:
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.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Macros are great for working with strings, and one of the most commonly used string functions is Len. This tip explains ...
Discover MoreIf you need to exit a macro before it is finished running, you can do it using a brute force method, or you can build in ...
Discover MoreDo you need to create a number of words or phrases where you only alter a few letters in each one? If the alterations ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments