names and numbers

[zipgun]

I exported my cell phonebook to csv and converted to xls.

The phone leaves the last name in lower case, so I ran "Proper". Now, "Bob and Carol" has "And" interposed, and I would like Home, Wk, Work, Hm, Cell, etc. in lower case. Search "And" and replace with "and" is not working. The find part alone will find "And" by the value. But the replace part seems to only allow the function by the formula. Can it be done?

A different approach also failed: Seems you can not highlight the last name and operate with "Shift f3" to change the case (right?).

I think there might be problems to import my doctored file back into the phone, but I would still like to know solutions for the above Excel issues.

A problem only indirectly related to Excel:
The phone software has a separate module where you push one button to synch the phonebook with Outlook. I could not believe that they could not synch with a spreadsheet, but maybe text handling in Excel is too tricky. (?)

[VBA Noob]

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

[zipgun]

=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")) ??

[VBA Noob]

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