Converting Text to Numbers

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/

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/

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))

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?

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.

@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

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

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

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.