Number Conversions

Location: http://www.mvps.org/dmcritchie/excel/numconv.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

This page contains some VBA macros. Assistance to Install a Macro or User Defined Function on my Formula page.

Conversion of numbers which appear correct but are not validly interpreted in Excel. Numbers with a right minus appear correct but are treated as Text. Numbers in US use period as decimal fraction separator, and commas as thousands list separator, which is the reverse of many other countries.

Fix Right Minus sign

Fix Right Minus -- A Worksheet solution

This is a continuation of the previous topic and example. Following is a Worksheet solution to display a single value from another cell.
  =IF(RIGHT(D12,1)="-",-1*LEFT(D12,LEN(D12)-1)+0,D12)

FixRightMinus -- A Macro solution

Sub FixRightMinus()
   'David McRitchie   2000-02-04 rev 2000-05-01
   ' rev. based on Dana DeLouis and Peter Surcouf
   ' prior to XL97 use  xlManual and xlAutomatic
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   On Error Resume Next
   For Each cell In Selection.Cells.SpecialCells(xlConstants, xlTextValues)
      cell.Value = CDbl(cell.Value)
   Next cell
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

Solution above was revised based on solution which uses CDbl and On Error Resume from (Peter Surcouf and Dana DeLouis), which converts entire sheet rather than selected range. In any case the following is redundant and is replaced by one line.

     If Right(cell.Value, 1) = "-" Then
       cell.Value = -1 * Left(cell, Len(cell.Value) - 1)
     End If

Change Postive to Negative and Negative to Postive (#chgsign)

Worksheet Solution

The following will work and should be a bit neater,
in the results, and it will retain formulas rather than
converting to values:
  Place  -1  in an empty cell
  Select the area to be changed
  Edit,  Paste Special,  Multiply
Unfortunately it will convert blank cells to zero

To avoid that you can after choosing the selection area
  Ctrl+G (edit, go),  Formulas
  Edit,  Paste Special,  Multiply
Doesn't matter what you choose for formulas it will
only process cells that look like numbers.

and then do the constants
  reselect the original selection area
  Ctrl+G (edit, go),  Constants
  Edit,  Paste Special,  Multiply

Progression of multiple changes to a formula
  =1+1
  =(1+1)*-1
  =((1+1)*-1)*-1

Progression of multiple changes to a constant
  3
  -3
  3

Macro Solution

This is one of those macros that will probably work okay for most things, and I expect it will work for any of my own stuff. The possible problem point is the attempt to undo a previous sign change to a formula in an attempt to keep formula size from continuous propagation of longer and longer formulas.

Sub ChangePlusMinus()
  'D.McRitchie, 2004-08-11
  '-- Additional code added to check parens to not misprocess
  '--   =(A1+1)*(B1+1) * -1   -- but not foolproof i.e. =((A1+1))*...
  '-- Will not treat  ="1"&"2"  as a number
  '-- Best for Constants and avoiding Empty cells
  Dim cell As Range, txt As String
  On Error Resume Next
  For Each cell In Intersect(Selection, _
       Selection.SpecialCells(xlCellTypeConstants, 1))
    cell.Value = cell.Value * -1
  Next cell
  For Each cell In Intersect(Selection, _
       Selection.SpecialCells(xlCellTypeFormulas, 1))
    If Right(cell.Formula, 6) = ") * -1" And _
         InStr(3, cell.Formula, "(") <= InStr(3, cell.Formula, ")") And _
         Left(cell.Formula, 2) = "=(" Then
        cell.Formula = "=" & Mid(cell.Formula, 3, Len(cell.Formula) - 8)
    Else
       cell.Formula = "=(" & Mid(cell.Formula, 2) & ") * -1"
    End If
  Next cell
End Sub

Manually Reentering to convert Text to Numbers

If you want to convert values to numbers:

Make the underlying format number, so that when text is changed to number it will be a number and align as a number.
If you have any specific character strings to eliminate you can use Replace (ctrl+h) with the replacement left empty. You can eliminate or replace such things as Tab character, or line Feed, or Carriage Returns.
The text values remain text values until reentered, since these values may have leading or trailing spaces you can select an empty cell, and copy it (ctrl+c) then reselect all of the problem area, Edit, Paste Special, ADD radio button.
This will convert '100 to 100
alternative: F2 then Enter for each cell
This will not convert '100 to 100
Note if you multiply by 1 and the cell containing the 1 is defined as text, the result will be text with the "numbers" reappearing as left aligned; whereas, multiplying by 1 is frequently used to convert text to numbers, since it would normally be in a General (or a number) cell.
Adding an empty cell is consistent in converting text to number regardless of whether the cell containing the null is General or Text.

ReenterAsNumber()

A frequent problem is in Copying and Pasting into Excel, where fractions get converted into dates. The ReenterTextAsNumber macro will convert Text to numbers. The idea is to format the Excel ahead of time as Text and then fix up the result afterwards.
Color has been added to help you see where changes occur. You should remove or comment those lines out for normal use.
Some results may look a little confusing. A few items were made to have leading or trailing spaces. The other area is that I am in the US for our dates are mm/dd/yy as far as Excel thinks, but I have my regional settings dates as mm/dd/yyyy which is what format General will use. Yes the fractional formats are actually what was generated even though my macro indicates "General".

D E F G H I

2 TEXT spaces Result r.Format As.Pasted 1-2-3 entry

3 -14 2 -14 General -14 -14

4 -1 1 -1 General -1 -1

5 -14.98 0 -14.98 General -14.98 -14.98

6 14- 0 -14 General 14- 14-

7 -14 0 -14 General -14 -14

8 =2*3 0 6 General 6 6

9 14+ 0 14 General 14+ 14+

10 14.98+ 0 14.98 General 14.98+ 14.98+

11 100 0 100 General 100 100

12 abc 0 abc General abc abc

13 1/16 0 1/16 # ??/?? 16-Jan 0.0625

14 3/8 0 3/8 # ?/? 08-Mar 0.375

15 0 1/16 0 1/16 # ??/?? 0 1/16 0 1/16

16 0 3/8 0 3/8 # ?/? 0 3/8 0 3/8

17 15/16 0 15/16 # ??/?? 15/16 0.9375

18 4/16 0 1/4 # ??/?? 16-Apr 0.25

19 2/8 0 1/4 # ?/? 08-Feb 0.25

20 8/30/00 0 08/30/2000 m/d/yy 08/30/2000 #DIV/0!

21 30/8/00 0 30/8/00 General 30/8/00 #DIV/0!

22 3/8/99 0 03/08/1999 m/d/yy 03/08/1999 0.003787879

The example shows text pasted as text, same text after processing by macro, same text pasted into formatted as General, and same text pasted with Lotus 1-2-3 transition entry (no good for dates).
Sub ReenterAsNumber()
'David McRitchie  2000-09-02
'Documented in  http://www.mvps.org/dmcritchie/excel/numconv.htm
Dim cell As Range
Dim i As Long, j As Long, txt As String
On Error Resume Next
For Each cell In Intersect(Selection, _
     Selection.SpecialCells(xlConstants, 1))
   cell.Interior.ColorIndex = 24 'lt.violet
   cell.NumberFormat = "general"
   cell.Value = cell.Value
Next cell
For Each cell In Intersect(Selection, _
     Selection.SpecialCells(xlConstants, xlTextValues))
   cell.Interior.ColorIndex = 19 'lt.yellow
   If Left(cell.Value, 1) = "=" Then
      cell.NumberFormat = "general"
      cell.Formula = cell.Formula
      GoTo nextcell
   End If
   j = 0
   For i = 1 To Len(cell.Text)
     If Mid(cell.Text, i, 1) = "/" Then j = j + 1
   Next i
   Select Case j
     Case 1   'Treat as Fraction not date
      txt = Trim(cell.Text)
      cell.NumberFormat = "general"
      If InStr(1, txt, " ", 0) = 0 Then
         cell.Value = "0 " & txt
      Else
         cell.Value = cell.Value
      End If
     Case 2  'Treat as date
         cell.NumberFormat = "general"
         cell.Value = cell.Value
     Case Else
         On Error Resume Next
         cell.Interior.ColorIndex = 35 'lt.green
         cell.NumberFormat = "general"
         cell.Value = 0 + Trim(cell.Value) + 0
         On Error GoTo 0
     End Select
nextcell:
Next cell
End Sub

Convert US/European Numbers with reversed commas/periods

You may not need this macro if you are importing your data with the Text Import Wizard, which comes into play with File|Open of a .txt file, or with various options under the data menu such as text to columns and get external data. On the last dialog where you indicate Column attributes /General/text/date/do not import column/ there is and additional option hidden under the Advanced Button that allows you to indicate what the separators for thousands and decimal are. The default is your regional settings but you can indicate here what the column actually is using.
USNumbers macro
Since this macro will only process TEXT values, and changes commas to periods, and periods to commas it should work in Europe and in the US, because each will recognize it's own numbers. You will have a problem if you make your numbers appear as text by prefixing a single quote to a valid number.
Pre XL97 use xlManual & xlAutomatic. Don't know but that application.translation might work faster. It is strange to me that I don't see a TRANSLATE in VBA to convert commas to periods and periods to commas.
Sub USNumbers()
   'David McRitchie  2000-05-10 misc  convert text numbers
   '    under wrong control settings to numbes
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   Dim origValue As String
   Dim newValue As String
   On Error Resume Next
   For Each cell In Selection.Cells.SpecialCells(xlConstants, xlTextValues)
      origValue = cell.Value
      newValue = ""
      For i = 1 To Len(origValue)
        If Mid(origValue, i, 1) = "." Then
           newValue = newValue & ","
         ElseIf Mid(origValue, i, 1) = "," Then
           newValue = newValue & "."
         Else
           newValue = newValue & Mid(origValue, i, 1)
         End If
      Next i
      On Error Resume Next
      cell.Value = CDbl(Trim(newValue))
      On Error GoTo 0
   Next cell
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

The following was posted 2000-02-28

The following was posted as a reply for problems with sorting that treated 1000 as text instead of a number. Excpect that not all of this is clearly included above -- will check later.

Look at your right alignment toolbar button and make sure it is
not selected.  Text would be left aligned by default.  This is just
a quick somewhat unreliable test, since spaces could still make
it look right aligned.   You should actually check if it is treated as
a number or text.

   Select the column
   Edit, GoTo   (ctrl+g)
   constants    &  only text chose of (Numbers, Text, Logicals, Errors)

Another test is to check that the length matches what you see
    =LEN(A1)
sometimes what look like spaces are actually other characters.
CHAR(160)  is a required blank (&rbl;  in HTML) which looks like
as space but is actually another character.   CHAR(160) is
frequently brought into Excel  with Copy or Cut and Paste.

To convert constants containing spaces in numbers,
such as using  F2, Enter   *or*  using  =TRIM(A1)
would correct you can do the following:

   select a truly blank cell
   copy    (ctrl+c)
   select the column with problem constants
   edit,  Paste Special,   Add

   Similar to results achieved by copying a cell with a 1 and
   using multiply  withing   Paste Special,  except that multiply
   will change previously  ISBLANK (empty) cells to 0 instead
   of leaving blank;  whereas,  adding a blank cell will leave
   cells with only spaces alone instead of making blank or
   changing to zero.    I prefer adding from an empty cell.

Just because something looks like a number does not mean that Excel
will treat it as a number.    Add two additional columns to
your sheet to help show up problem.   Use fill handle to copy down.

   E1:  '=LEN(n)
   F1:  '=LEN(n)-LEN(TRIM(n))
   G1:  '=ISNUMBER(n)
   E2:  =LEN(A2)
   F2:  =LEN(A2)-LEN(TRIM(A2))
   G2:  =ISNUMBER(A2)

Fill-Handle, to quickly populate cells with same data or in a serial fashion.
Inserting Rows and some checkbook considerations. and techniques for moving rows or columns; and inserting blank rows or columns all using the cursor in combination with the ctrl, shift, or alt keys
Reenter, reenter data without leading/trailing spaces or reenter data involved with changed formatting, see Reenter subtopic of Rearranging Data in Columns which primarily deals with rearranging and reformatting data.
Strings, includes conversion of numbers to check protection words.
Q287027 XL2002: Web Query Converts Numbers That Contain Hyphens into Dates. solution: Excel 2002 provides a long needed option to Disable date recognition when importing data.

You are one of many distinguished visitors who have visited my site here or in a previous location since this page was created on May 10, 2000.

Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

	D	E	F	G	H	I
2	TEXT	spaces	Result	r.Format	As.Pasted	1-2-3 entry
3	-14	2	-14	General	-14	-14
4	-1	1	-1	General	-1	-1
5	-14.98	0	-14.98	General	-14.98	-14.98
6	14-	0	-14	General	14-	14-
7	-14	0	-14	General	-14	-14
8	=2*3	0	6	General	6	6
9	14+	0	14	General	14+	14+
10	14.98+	0	14.98	General	14.98+	14.98+
11	100	0	100	General	100	100
12	abc	0	abc	General	abc	abc
13	1/16	0	1/16	# ??/??	16-Jan	0.0625
14	3/8	0	3/8	# ?/?	08-Mar	0.375
15	0 1/16	0	1/16	# ??/??	0 1/16	0 1/16
16	0 3/8	0	3/8	# ?/?	0 3/8	0 3/8
17	15/16	0	15/16	# ??/??	15/16	0.9375
18	4/16	0	1/4	# ??/??	16-Apr	0.25
19	2/8	0	1/4	# ?/?	08-Feb	0.25
20	8/30/00	0	08/30/2000	m/d/yy	08/30/2000	#DIV/0!
21	30/8/00	0	30/8/00	General	30/8/00	#DIV/0!
22	3/8/99	0	03/08/1999	m/d/yy	03/08/1999	0.003787879