I have a sheet that I export to excel from an outside program. It brings a column of numbers in with a space in front. The space seems to be 1/2 width, and trimming it doesn't remove it. Is there a way to tell what the space is, and remove it? I know I can do a find/replace using just that space, but I would like to make this easier on people who will be using this tool.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Removing preceeding space from number
- Thread starter tightwad
- Start date
daniels012
Well-known Member
- Joined
- Jan 13, 2005
- Messages
- 5,219
=trim(A1)
should work
Michael
should work
Michael
Upvote
0
Oaktree
MrExcel MVP
- Joined
- Jun 20, 2002
- Messages
- 8,120
- Office Version
- 365
Try putting =CHAR(ROW(A1)) in cell Z1 (or some unused column). Copy Z1 down through row 255.
Then, use =MATCH(LEFT(A1),Z1:Z255,0) to determine the CHAR number of the space that is being imported.
You can then use find and replace or a formula like =SUBSTITUTE(A1,CHAR(whatever character the space represents),"") to remove the imported character.
Then, use =MATCH(LEFT(A1),Z1:Z255,0) to determine the CHAR number of the space that is being imported.
You can then use find and replace or a formula like =SUBSTITUTE(A1,CHAR(whatever character the space represents),"") to remove the imported character.
Upvote
0
Scott Huish
MrExcel MVP
- Joined
- Mar 17, 2004
- Messages
- 20,381
- Office Version
- 365
- Platform
- Windows
Why not just use CODE to get the number
=CODE(LEFT(A1))
If the character is ALWAYS there, you could do this:
=REPLACE(A1,1,1,"")
=CODE(LEFT(A1))
If the character is ALWAYS there, you could do this:
=REPLACE(A1,1,1,"")
Upvote
0
parry
MrExcel MVP
- Joined
- Aug 20, 2002
- Messages
- 3,355
First try multiplication coercian. Enter 1 into a blank cell then copy this cell. Next select the target cells and select Edit | Paste Special and choose the multiply operation.
TRIM and CLEAN do not pick up ASCII characters 127 and 160 which sometimes come over from mainframe extracts. If the multiplication trick above doesnt work then use this function to clean the cells then paste special values back over the result and delete the old column.
To add code...
1. Open the Visual Basic Editor (ALT+F11 or Tools|Macro|Visual Basic Editor from the menu)
2. Select Insert|Module from the menu
3. Paste the code in the right-hand window
4. Close the Visual Basic Editor (ALT+Q or File|Close and return to Microsoft Excel from the menu)
Use function...
=MEGACLEAN(A1)
TRIM and CLEAN do not pick up ASCII characters 127 and 160 which sometimes come over from mainframe extracts. If the multiplication trick above doesnt work then use this function to clean the cells then paste special values back over the result and delete the old column.
To add code...
1. Open the Visual Basic Editor (ALT+F11 or Tools|Macro|Visual Basic Editor from the menu)
2. Select Insert|Module from the menu
3. Paste the code in the right-hand window
4. Close the Visual Basic Editor (ALT+Q or File|Close and return to Microsoft Excel from the menu)
Use function...
=MEGACLEAN(A1)
Code:
Function MEGACLEAN(varVal As Variant)
Dim NewVal As Variant
If IsMissing(varVal) Then Exit Function
NewVal = Trim(varVal) 'remove spaces
NewVal = Application.WorksheetFunction.Clean(NewVal) 'remove most unwanted characters
NewVal = Application.WorksheetFunction.Substitute(NewVal, Chr(127), "") 'remove ASCII#127
NewVal = Application.WorksheetFunction.Substitute(NewVal, Chr(160), "") 'remove ASCII#160
MegaClean = NewVal
End Function
Upvote
0
parry
MrExcel MVP
- Joined
- Aug 20, 2002
- Messages
- 3,355
Hi, the following macro will clean up all the cells in a selection. Select all the cells in your range and run the CleanSelection macro.
regards,
Graham
regards,
Graham
Code:
Sub CleanSelection()
Dim c As Range, Rng As Range
Set Rng = Intersect(Selection, Selection.Parent.UsedRange)
If Rng Is Nothing Then
MsgBox "No cells with values!"
Exit Sub
End If
For Each c In Rng
If Not IsError(c) Then
c.Value = MEGACLEAN(c)
End If
Next c
End Sub
Function MEGACLEAN(varVal As Variant)
Dim NewVal As Variant
If IsMissing(varVal) Then Exit Function
NewVal = Trim(varVal) 'remove spaces
NewVal = Application.WorksheetFunction.Clean(NewVal) 'remove most unwanted characters
NewVal = Application.WorksheetFunction.Substitute(NewVal, Chr(127), "") 'remove ASCII#127
NewVal = Application.WorksheetFunction.Substitute(NewVal, Chr(160), "") 'remove ASCII#160
MEGACLEAN = NewVal
End Function
Upvote
0
parry
MrExcel MVP
- Joined
- Aug 20, 2002
- Messages
- 3,355
My pleasure. Get yourself a good book and then use this forum to help when needed.
If you want to understand my code above heres a couple of things that may not be obvious why I am doing them...
Set Rng = Intersect(Selection, Selection.Parent.UsedRange)
Selection is the range of cells selected by the user. What I am doing here is comparing that selection to cells that have something in them. Why? Because if I didnt then when I loop through cells to clean them up I may be looking at millions of cells I dont need to and so the macro would take forever to run (there are 16M+ in a sheet).
IsError(c)
Not probably an issue in your case but if a cell has a formula error (eg #DIV/0! etc) then when you use the Value property in c.Value = MEGACLEAN(c) it will fail so instead I am skipping any cell with an error.
regards,
Graham
If you want to understand my code above heres a couple of things that may not be obvious why I am doing them...
Set Rng = Intersect(Selection, Selection.Parent.UsedRange)
Selection is the range of cells selected by the user. What I am doing here is comparing that selection to cells that have something in them. Why? Because if I didnt then when I loop through cells to clean them up I may be looking at millions of cells I dont need to and so the macro would take forever to run (there are 16M+ in a sheet).
IsError(c)
Not probably an issue in your case but if a cell has a formula error (eg #DIV/0! etc) then when you use the Value property in c.Value = MEGACLEAN(c) it will fail so instead I am skipping any cell with an error.
regards,
Graham
Upvote
0
Similar threads
- Solved
- Question
