Alternatively, you can use this UDF pasted into a
VB Module; ALT +
F11InsertModule
Function GetWord(ByVal txt, Optional start As Integer, Optional q As
Integer) As String
Dim tmp As String, i As Integer, str() As String
txt = Application.Substitute(txt, ",", "")
str() = Split(txt, " ")
If start = 0 Then
'Just remove commas
GetWord = txt 'this is OK
ElseIf start = 1 And q 1 Then
For i = start - 1 To start + q - 2
tmp = tmp & str(i) & " "
Next i
GetWord = Trim(tmp)
Exit Function
ElseIf start = 1 And q = 0 Then
GetWord = str(start - 1)
End If
End Function
if A15 contains:= James Graham, 16 the Low Road
then =getword(A15) returns:
James Graham, 16 the Low Road (commas removed)
=getword(A15,1,2) returns:
James Graham
=getword(A15,3,4) returns:
16 the Low Road
and Getword(A15,1) returns:
James
you should be able to build a good list with these formulas.
Best of luck
Peter
Now I would like to transfer JUST the names and last name to another cell.
Is it possible to do that? if so, how?
I don't want to erase 700 address of each cell and then copy and paste the
name/last name. That will take a lot of time.
Could somebody guide me? please, I am willing to learn.
Regards.