Solved: Convert String?

[Blackie50]

Hi,

Is there any way to convert a capitalized full name to the following either by formula or code e.g

MR P BLOGGS to Mr Bloggs
MRS R FALKINGHAM to Mrs Falkingham

I tried using left/right/lower but the numbers to convert change each time

thanks for any help
Jon

[Bob Phillips]

Try

=PROPER(LEFT(A2,FIND(" ",A2)))&" "&PROPER(RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2&" "," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

[Blackie50]

Hi thanks for swift reply.

Sorry wasn't specific enough - want to type

MR P BLOGGS (or whatever) in to A1 on sheet1 and with the aid of the formula change it to Mr Bloggs in A1 on sheet2

regards
Jon

[Bob Phillips]

Then just change all my A2 to Sheet1!A1 - basic stuff.

[Blackie50]

Yep sorry Bob - wasn't concentrating

thanks again

[Blackie50]

Great - works like a charm

regards

Jon