names and numbers

Hi,

Say your names strated in A1 then enter this in the next blank cell in row 1

=PROPER(SUBSTITUTE(A1," and",""))

It converts Bob and carol to Bob Carol.

VBA Noob

=PROPER(SUBSTITUTE(A1," and",""))


Ok, that is one approach, but note that it may be Bob Smith and Carol Jones, so Bob and Carol is more representative and more likely to be typed into the phone (one point of origin).

=PROPER(SUBSTITUTE(A1," And"," and")) ??

Hi,

The below array formula (enter with Ctrl + Shift + Enter) would extract Bob Carol for this example.

It does this by returning the first word and the 2nd last word in a string so doesn't work for your orginally post

=LEFT(A1,FIND(" ",A1,1))&" "&MID(A1,SMALL(IF(MID(" "&A1,ROW(INDIRECT("1:"&LEN(A1)+1)),1)=" ",ROW(INDIRECT("1:"&LEN(A1)+1))),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))),SUM(SMALL(IF(MID(" "&A1&" ",ROW(INDIRECT("1:"&LEN(A1)+2)),1)=" ",ROW(INDIRECT("1:"&LEN(A1)+2))),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+{0,1})*{-1,1})-1)

VBA Noob