How do I find last occurence of a character in a text string in Ex

[jten]

I am looking for the last occurrence of a blank in a text string so that I
can parse the string

 

[RB Smissaert]

The VBA function InStrRev will do that.

RBS

 

[Mike H]

Hi,

You posted in programming so maybe you want this.

mypos = InStrRev(Range("A1"), " ")

bur if you wanted a worksheet solution try this

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

Mike

 

[Rick Rothstein]

Since you are asking about finding the last blank in a text string so that
you can parse the string, I am assuming you are looking to obtain the last
word in the string. If that is the case, you can do it like this...

Programming Method #1
========================
Txt = "Your Text String"
LastWord = Mid(Txt, InStrRev(Txt, " ") + 1)

Programming Method #2
========================
Txt = "Your Text String"
Arr = Split(Txt)
LastWord = Arr(UBound(Arr))

When you Dim your variables, Programming Method #2 can use either a Variant
or a String array for the Arr variable shown.

Formula Method
========================
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

The formula method assumes your text will be no longer than 99 characters.
If it will be longer, then change the two 99's to a number larger than the
maximum length.

 

[Patrick Molloy]

as well as the two responses already received, you could also use the SPLIT
function

This is particularly useful if you want to see how many items are in the text

eg
Option Explicit
Sub SplitDemo()
Dim text As String
Dim var As Variant
text = "There are four words"
var = Split(text, " ")

MsgBox "text has " & UBound(var, 1) + 1 & " items"

Dim index As Long
index = UBound(var, 1)
MsgBox "The #" & index & " item =" & var(index)

End Sub