Take out space

[kmlartigue]

There are some values that have a space and then the ticker. For example in cell D the value is " HD". Is there a way to remove just the first empty space?

[Bob Phillips]

=TRIM(D2)

[mdmackillop]

If that fails, try =CLEAN(D2). Sometimes a space is a non-printing character such as CHR(160)

[Paul_Hossler]

Actually, CLEAN() or TRIM() won't remove 160's

From online help:



Important: The CLEAN function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters. For an example of how to remove these additional nonprinting characters from text, see Remove spaces and nonprinting characters from text (https://support.office.com/en-US/f1/Topic/Remove-spaces-and-nonprinting-characters-from-text-023f3a08-3d56-49e4-bf0c-fe5303222c9d).


FWIW, I have a UDF to 'really' clean a string




'Ref: MS Excel 2007 Help, Remove spaces and nonprinting characters from text
Function strClean(s As String, Optional MakeUpper As Boolean = False) As String
Dim s1 As String

With Application.WorksheetFunction

'non-printable characters NOT caught by CLEAN
s1 = .Substitute(s, Chr(160), Chr(32)) ' non-breaking space
s1 = .Substitute(s1, Chr(127), Chr(7)) ' ASCII 7 = BEL char
s1 = .Substitute(s1, Chr(129), Chr(7))
s1 = .Substitute(s1, Chr(141), Chr(7))
s1 = .Substitute(s1, Chr(143), Chr(7))
s1 = .Substitute(s1, Chr(144), Chr(7))
s1 = .Substitute(s1, Chr(157), Chr(7))
'remove leading, trailing, multiple spaces (inc. what was 160's)
s1 = .Trim(s1)
'remove 0 - 31 (inc. what was 127, 129, 141, 143, 144, and 157)
If MakeUpper Then
strClean = UCase(.Clean(s1))
Else
strClean = .Clean(s1)
End If
End With
End Function

[mdmackillop]

Much ado about nothing! :devil2:

[SamT]

Sometimes what you don't see can hurt you. :D

[snb]

why using a worksheetfunction, where VBA provides a similar method ?


Function F_snb(c00 As String)
F_snb=application.clear(c00)

for j=1 to 7
F_snb=replace(F_snb,chr(choose(j,160,127,129,141,143,144,157))," ")
next

F_snb=application.trim(F_snb)
End Function

[kmlartigue]

I'm testing all of these to see what works best.

[kmlartigue]

The =TRIM(D2) does not remove the space from the very beginning.

[kmlartigue]

=Clean(D2) does not seem to remove the beginning space either.

[kmlartigue]

I am trying to remove the space in the beginning entirely. Right now data in a cell looks like " H2". Trying to just delete that first character(the space)

[kmlartigue]

What about a similar code to the one I used to find the data between ( ). Could I rewrite this a bit to say find the data between empty two spaces on either end?

Sub Extractaccountnum()
With ActiveSheet

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 3 To LastRow

If .Cells(i, "B") <> " " Then

.Cells(i, "K").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-6],FIND(""("",RC[-6])+1,FIND("")"",RC[-6])-FIND(""("",RC[-6])-1)"
End If
Next i
End With

End Sub

[Paul_Hossler]

I am trying to remove the space in the beginning entirely. Right now data in a cell looks like " H2". Trying to just delete that first character(the space)

Maybe it's not really a space

In a empty cell enter =CODE(D2)

A space = 32

[snb]

What about posting a file ?

[kmlartigue]

The =CODE(D2) returns a number. In cell D2 I have the value " H2" and =CODE returns a number of 160?

[Paul_Hossler]

ASCII 160 is a 'non-breaking space' -- web sites use them a lot -- but CLEAN() and TRIM leave them alone

Try this formula -- note the double dashes in front that are used to force it to be a number like in D1 of the attachment



=--TRIM(SUBSTITUTE($A$1,CHAR(160),CHAR(32)))



20100

[kmlartigue]

I get an error when I try the above code.

[kmlartigue]

I did this code =--TRIM(SUBSTITUTE(D407,CHAR(160),CHAR(32))) on Cell D407 which contains " HD" and get a #VALUE! error.

[kmlartigue]

See the attached test sheet. There is a space in front of AAA in column D and a space in front of Buy in column E. I want to remove the space in the beginning.

[kmlartigue]

Something like this works manually where you select the data in D3 and go to Data - Text to Columns - Fixed Width - Next - put your break line right in front of the text to get rid of that extra space in the beginning and then select general and say finish.

Selection.TextToColumns Destination:=Range("X3"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1)), TrailingMinusNumbers:=True

For each cell in D that we are adjusting, how can I say to put that in the corresponding row in column X. Right now, it goes down each cell in D and keeps placing each result in X3, overriding it each time. Is there a way to do this fixed width for each cell in column D and place the results in the corresponding row in column X?

[kmlartigue]

This code doesn't work but would something like this work? Where you say for each cell in column D down to the lastrow select cell in column X and place the fixed width result in column x?


Sub TrimD()
With ActiveSheet

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 3 To LastRow

If .Cells(i, "B") <> "" Then

.Cells(i, "X").Select
ActiveCell.selection.TextToColumns Destination:=Range("i, "D"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1)), TrailingMinusNumbers:=True
End If
Next i
End With
End Sub

[kmlartigue]

I think I figured it out.


Sub TrimD()
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("Y3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("Y3"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1)), TrailingMinusNumbers:=True
Range("Z3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("D3").Select
ActiveSheet.Paste
Range("A1").Select

End Sub

[mdmackillop]

Sub Del160()
Set r = Cells.SpecialCells(2)
Do
Set c = r.Find(Chr(160), lookat:=xlPart)
If Not c Is Nothing Then c.Value = Replace(c, Chr(160), "")
Loop Until c Is Nothing
End Sub