Trim special charcters from start of string

[John Michl]

Is there a function or quick VBA routine that will remove and CHR(10)
or CHR(13) characters from the begining of a string.

If not, how about someway to find the first non-special character in a
string?

Thanks for the help.

- John

 

[Guest]

Try Excels CLEAN function

strTemp = application.clean(strTemp)

 

[John Michl]

Thanks. That almost works. I only want to remove those characters if
they are before the first real text in a string. Using CLEAN on my
string eliminates all of the carriage returns later on the string that
I want to keep. Something like LTRIM would be perfect but there
doesn't seem to be a LCLEAN.

What I'm trying to do is retrieve text from a text box that has been
edited by the user. I need someway to determine what position contains
the first non-special character (i.e., real text) so that I can start
the retrieval at that point.

- John

 

[Guest]

Maybe something like this would work better. Select the cells and run. Just
be sure to keep a backup of your data.

Sub test()
Dim strTemp As String
Dim i As Long
Dim rngCell As Range

For Each rngCell In Selection.Cells
If Not IsEmpty(rngCell) Then
strTemp = rngCell.Value
For i = 1 To Len(strTemp)
If Asc(Mid(strTemp, i, 1)) <> 10 And _
Asc(Mid(strTemp, i, 1)) <> 13 Then
strTemp = Right(strTemp, Len(strTemp) - i + 1)
Exit For
End If
Next i
rngCell.Value = strTemp
End If
Next rngCell

End Sub

 

[Guest]

If the characters are in the other order than you specified, i.e. Chr(13)
then Chr(10) (CarriageReturn/LineFeed) you can do this

Do While Left(MyStr, 2) = vbCrLf
MyStr = Mid(MyStr, 3)
Loop

 

[John Michl]

Thanks for the help Charlie and JMB.

My application is used to retrieve the contents of user edited text
boxes and strip out the early CR entered with the "Enter" key. I
played around with your ideas and came up with the following that
identifies the first non-CR character in the text box.

Sub FindNonCR
Dim shp As Shape
Set shp = Sheets("Assumptions").Shapes("tbNote_Test")
l = shp.TextFrame.Characters.Count

i = 1

Do While shp.TextFrame.Characters(Start:=i, Length:=1).Text = Chr(10)
If shp.TextFrame.Characters(Start:=i, Length:=1).Text = Chr(10)
Then
MsgBox "Position " & i & " is CRLF"
Else
MsgBox "Position " & i & " is " &
shp.TextFrame.Characters(Start:=i, Length:=1).Text
End If
i = i + 1
Loop

MsgBox "First non-CR is at position " & i

End Sub

For my actual app I'll only need the value (not the message boxes).

- John