Problem with trailing blanks in a string

[blueflash]

I am using VBVA and am trying to split inputstrings into separate fields.

Sub strread()
Dim arrsplitstring() As String
Dim temp As String
    For i = 1 To 2
        inputstring = Cells(i, "a").Value
         arrsplitstring = Split(inputstring)
    Next i
End Sub

The input data are

When I input A1 and look at the 3rd field (71) in the Locals window, it is shown correctly as "71".
When I input A2 and look at the 3rd field it is shown as "64 " ie, there is a trailing blank.
Does anyone know why this is happening?
In further code, I have tried to remove this blank using TRIM and RTRIM but the blank still remains.
Is there another way of getting rid of the trailing blank?

 

[Zot]

Maybe it is not blanks but unprintable characters that is not displayed. Refer to this

VBA Remove all Non-Printable and special characters as well as Trim

Can someone help me with a macro to combine Removing all Non-Printable and special characters as well as Trim. I'm using a trim macro at the moment and works great but isn't always removing hidden characters. Also could probably be better than this. Sub Trim() ' ' Trim Macro ' Trim '...

www.mrexcel.com

 

[Alex Blakenburg]

See if you are lucky and if this fixes it.

    inputstring = Replace(Cells(i, "a").Value, ChrW(160), "")

Although if that works is will probably be faster to do it to the whole of Column A right at the start.
The format would be something along these lines:
(you would need to change the range)

Range("a1").CurrentRegion.Replace What:=ChrW(160), Replacement:="", lookat:=xlPart

 

[blueflash]

    inputstring = Replace(Cells(i, "a").Value, ChrW(160), "")

Range("a1").CurrentRegion.Replace What:=ChrW(160), Replacement:="", lookat:=xlPart

Thanks Alex, the first alternative deleted all trailing blanks ok.

I couldn't get the second alternative to work - it deleted all blanks from the input string so it basically couldn't be split into different fields.
I can work with the first alternative so will use that.
Thanks

 

[Alex Blakenburg]

If you want us to trouble shoot it we would need an XL2BB of a sample of your data or a link to a sample of your data on a sharing platform such as google drive, dropbox etc.
If the first worked, it proved that you have a non-breaking space character (code 160).
Doing the replace on the whole of Column A before you do the rest of the macro should just be faster than doing it one line at a time.

If you have a numeric keypad, try it manually if you like.
In Excel, highlight column A then go Ctrl+H for replace.
In the find box type in Alt+0160 (using the "numeric keypad"), you won't see anything but the cursor should move over 1 character.
In the replace box - leave it blank.
Replace All

Then run your original macro.

 

[Zot]

Did you tried the UDF by @Rick Rothstein in the link?

Sub strread()
Dim arrsplitstring() As String
Dim temp As String
    For i = 1 To 2
        inputstring = Cells(i, "a").Value
        inputstring = CleanTrim(inputstring)
        arrsplitstring = Split(inputstring)
    Next i
End Sub

Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
  Dim X As Long, CodesToClean As Variant
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
  If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
  For X = LBound(CodesToClean) To UBound(CodesToClean)
    If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
  Next
  CleanTrim = WorksheetFunction.Trim(S)
End Function

 

[blueflash]

Did you tried the UDF by @Rick Rothstein in the link?

Sub strread()
Dim arrsplitstring() As String
Dim temp As String
    For i = 1 To 2
        inputstring = Cells(i, "a").Value
        inputstring = CleanTrim(inputstring)
        arrsplitstring = Split(inputstring)
    Next i
End Sub

Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
  Dim X As Long, CodesToClean As Variant
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
  If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
  For X = LBound(CodesToClean) To UBound(CodesToClean)
    If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
  Next
  CleanTrim = WorksheetFunction.Trim(S)
End Function

Did you tried the UDF by @Rick Rothstein in the link?

Sub strread()
Dim arrsplitstring() As String
Dim temp As String
    For i = 1 To 2
        inputstring = Cells(i, "a").Value
        inputstring = CleanTrim(inputstring)
        arrsplitstring = Split(inputstring)
    Next i
End Sub

Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
  Dim X As Long, CodesToClean As Variant
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
  If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
  For X = LBound(CodesToClean) To UBound(CodesToClean)
    If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
  Next
  CleanTrim = WorksheetFunction.Trim(S)
End Function

Thanks, Zot. The one-line solution offered by Alex has solved my problem. If I have more problems with non-printing characters, I will look at the UDF in more detail but at the moment, I seem to be ok.

 

[blueflash]

If you want us to trouble shoot it we would need an XL2BB of a sample of your data or a link to a sample of your data on a sharing platform such as google drive, dropbox etc.
If the first worked, it proved that you have a non-breaking space character (code 160).
Doing the replace on the whole of Column A before you do the rest of the macro should just be faster than doing it one line at a time.

If you have a numeric keypad, try it manually if you like.
In Excel, highlight column A then go Ctrl+H for replace.
In the find box type in Alt+0160 (using the "numeric keypad"), you won't see anything but the cursor should move over 1 character.
In the replace box - leave it blank.
Replace All

Then run your original macro.

Alex, speed is not an issue fro me so I will stick to the easier one-line solution. Re your manual suggestion, I am compounding my problems by using a non Apple keyboard and am not sure how to type in alt+160. I will have to get out my old Apple keyboard to try that out.