# Number Conversions

Location:   http://www.mvps.org/dmcritchie/excel/numconv.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
'--   =(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:
1. Make the underlying format number, so that when text is changed to number it will be a number and align as a number.
2. 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.
3. 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

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

```

## Related

• 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.