asked on

Remove Leading and Trailing Spaces From a String

Hello All. First let me start by saying "TRIM" DOES NOT Work.

Using Excel VBA, I used the InStr Function to separate the ID Numbers using the comma.
The problem is, this data is in an Excel Cell. The guy used the Alt + Enter to move to the next line after adding the comma.
When I pull it into a variable, I get one of two results.
" Paragragh 3.5.3" or "Paragragh 3.5.3 "
It has a blank space before or after each one. I am not sure it if is a Carriage Return or not.
I have not been able to get rid of it. I need that term to use a search. Because of the extra spaces, they are not being found even it they are there.

Fake Data:

PUNISHER1.2,3,
CYCLOPS,
VISION,
HULK,
GHOST RIDER,
THOR

strLeftToCaptureCheck = Left(strToCapture, 1)
If strLeftToCaptureCheck = "" Then
MsgBox "Check"
End If

As a test I used the code above to see if there was something there.
The result was strLeftToCaptureCheck = "". The "" is not a blank space. It skipped over the message box.

Is there a way to find the first character position or just get rid of whatever those blank space are?
I never know if they are going to show in the beginning or the end. I need a way to look at both ends.

Thanks
Nate

Martin Liss

Well Trim(strToCapture) will do it. but only if they are spaces. Can you attach a workbook?

Martin Liss

I created a test file and added data using Alt+Enter and I see no evidence that any invisible characters were added so do this

strLeftToCaptureCheck = Trim(strToCapture)
  If strLeftToCaptureCheck = "" Then ' Note no space between quotes
            MsgBox "Check"
  End If

Open in new window

WonHop

ASKER

Sorry, the company will not allow me to attach the workbook. I was hoping someone could type some data into Excel using the Alt + Enter to move to the next line and see what they get.
They are not spaces. Could they be Carriage Return? I tried looking for VbCrLf. I got no results from that. I might be using the wrong search criteria.
Is there a way to find the first and/or last character in a string.
When I use the Len function, it counts the extra space.

Thanks

Martin Liss

My code above can be shortened to

  If Trim(strToCapture) = "" Then ' Note no space between quotes
            MsgBox "Check"
  End If

Open in new window

WonHop

ASKER

You won't see it in the variable. You will need to add it to the Watch Window to see it.
See attached photo.

Thanks
ACaptureSnapShot.JPG

Martin Liss

Is there a way to find the first and/or last character in a string.

Dim intChar As Integer

For intChar = 1 To Len(strToCapture)
    Debug.Print "Char:" & intChar & " " & Mid$(strToCapture, intChar, 1)
Next

Open in new window

The press Alt+F11 to get to VBA and the press Ctrl+g to see the Immediate Window.

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

WonHop

ASKER

Below are the results of the two searches. It looks like it is "Asc 10" - "NL". "New Line" that was giving me the problem.

This is the Final Code that gets me what I need: See the Final Results in the attached Photo.

intLenChr10 = Len(strToCapture)
intInstChr10 = InStr(strToCapture, Chr(10))
intRightChr10 = Right(strToCapture, intLenChr10 - intInstChr10)

Char:1
Char:2 P
Char:3 R
Char:4 O
Char:5 P
Char:6 .
Char:7 0
Char:8 7
Char:9 5
Char:10 6

Char:1
Asc 10
Char:2 P Asc 80
Char:3 R Asc 82
Char:4 O Asc 79
Char:5 P Asc 80
Char:6 . Asc 46
Char:7 0 Asc 48
Char:8 7 Asc 55
Char:9 5 Asc 53
Char:10 6 Asc 54

Thank You Very Much.

WonHop

ASKER

Here is the Final Results Photo
Thanks
ASCII_10_InStr_Results.JPG

WonHop

ASKER

New Final code with str instead of int. :0)
intLenChr10 = Len(strToCapture)
intInstChr10 = InStr(strToCapture, Chr(10))
strRightChr10 = Right(strToCapture, intLenChr10 - intInstChr10)

Martin Liss

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
Experts Exchange Top Expert VBA (current)

Wayne Taylor (webtubbs)

Using Excel VBA, I used the InStr Function to separate the ID Numbers using the comma.

Use the Split function to return an array of strings instead. And use it in conjunction with the Replace function, replacing vbLf (Asc code 10) with "".

stefri

Clean function maybe?
The Excel CLEAN function takes a text string and returns text that has been "cleaned" of line breaks and other non-printable characters. You can use CLEAN to remove characters that are not printable from text. You can also use CLEAN to strip line breaks from text.