How to ID the value of a character in a string

[Mark]

I have a cell containing a text value and I see I can use the characters
method to format a particular character although that doesn't appear to
support getting a value.

Anyone know how to try each value in a string in turn (I want to get each
name in a 3 part name so am referencing the space in between each to id each
name).

Help much appreciated after an hour on the help i can't figure it out.

Kind regards, Mark

 

[Frank Kabel]

Hi Mark
in VBA have a look at the split function. e.g.
sub foo
dim names
dim i
dim total_str as string
total_str = "First Middle Last"
names = split(total_str," ")
for i = 0 to ubound(names)
msgbox names(i)
next
end sub

 

[Bob Phillips]

Be aware that Split doesn't work in XL97.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Sub BBB()
Dim sStr As String
Dim sStr1(1 To 3)
Dim i As Long, j As Long
sStr = "abc ropqefg hijklm"
i = 1
For j = 1 To Len(sStr)
If Mid(sStr, j, 1) = " " Then
i = i + 1
Else
sStr1(i) = sStr1(i) & Mid(sStr, j, 1)
End If
Next
For i = 1 To 3
Debug.Print sStr1(i)
Next
End Sub