|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.
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)
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 SubSolution 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
Worksheet SolutionThe 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 SolutionThis 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
If you want to convert values to numbers:
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.
- 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
Adding an empty cell is consistent in converting text to number regardless of whether the cell containing the null is General or Text.
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
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 macroSince 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 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)
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
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved