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
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
Try Excels CLEAN function
strTemp = application.clean(strTemp)
"John Michl" wrote:
> 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
>
>
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
JMB wrote:
> Try Excels CLEAN function
>
> strTemp = application.clean(strTemp)
>
>
>
> "John Michl" wrote:
>
> > 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
> >
> >
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
"John Michl" wrote:
> 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
>
> JMB wrote:
> > Try Excels CLEAN function
> >
> > strTemp = application.clean(strTemp)
> >
> >
> >
> > "John Michl" wrote:
> >
> > > 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
> > >
> > >
>
>
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" wrote:
> 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
>
> JMB wrote:
> > Try Excels CLEAN function
> >
> > strTemp = application.clean(strTemp)
> >
> >
> >
> > "John Michl" wrote:
> >
> > > 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
> > >
> > >
>
>
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
Charlie wrote:
> 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" wrote:
>
> > 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
> >
> > JMB wrote:
> > > Try Excels CLEAN function
> > >
> > > strTemp = application.clean(strTemp)
> > >
> > >
> > >
> > > "John Michl" wrote:
> > >
> > > > 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
> > > >
> > > >
> >
> >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks