PDA

View Full Version : Solved: Convert String?



Blackie50
03-22-2012, 07:09 AM
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
03-22-2012, 07:59 AM
Try

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

Blackie50
03-22-2012, 08:21 AM
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
03-22-2012, 08:40 AM
Then just change all my A2 to Sheet1!A1 - basic stuff.

Blackie50
03-22-2012, 09:01 AM
Yep sorry Bob - wasn't concentrating

thanks again

Blackie50
03-23-2012, 01:50 AM
Great - works like a charm

regards

Jon