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

  • Thread starter Thread starter jten
  • Start date Start date
J

jten

I am looking for the last occurrence of a blank in a text string so that I
can parse the string
 
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
 
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.
 
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
 
Back
Top