Removing preceeding space from number

[tightwad]

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.

 

[daniels012]

=trim(A1)
should work

Michael

 

[Oaktree]

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.

 

[Scott Huish]

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

 

[Oaktree]

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

(sigh). Yes, you *could* just use the CODE function.

 

[parry]

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)

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

 

[tightwad]

It appears it is code 160....this is all new to me. I have a couple columns this applies to. What do I need to set this as a command that will clean the whole sheet?

 

[parry]

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

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

 

[tightwad]

Awesome, that worked beautifully...I am gather quite the collection of Macro's...next I will learn to write the code...

 

[parry]

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