1
1
I have a list of (around 1200) names in MS Excel which are in several different formats.
- Full names with 3 parts - ex: John Maynard Keynes
- Full names with 4 parts - ex: Mohomad Hussain Mohomad Niyas
- Names with initials - ex: S R Wilson, A john
- Names with salutations - ex: Miss T Anojani, Mr Thilan Kumara, Master A H Vidushan
(Yep! It's a mess)
I need to convert it to the format below.
J. M. Keynes
M. H. M. Niyas
S. R. Wilson
A. John
T. Anojani
T. Kumara
A. H. Vidushan
I tried these formulas, but not the exact result is returned!
=LEFT(A1)& ". " & IF(ISNUMBER(FIND(" ",A1)),MID(A1,FIND(" ",A1)+1,1)," ") & ". " & IF(ISNUMBER(FIND(" ",A1)),MID(A1,FIND(" ",A1)+1,1)," ")& ". " & IF(ISNUMBER(FIND(" ",A1,FIND(" ",A1)+1)), RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),"")
=LEFT(A2)&IF(ISNUMBER(FIND(" ",A2)),MID(A2,FIND(" ",A2)+1,1)," ")&IF(ISNUMBER(FIND(" ",A2,FIND(" ",A2)+1)),MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1)," ")
I guess the first formula will work if I'm able to loop the check for the space between names, until all the components of the name are traversed. But can't figure out how to do that!
CRoshanLG
Posted 2017-01-11T05:20:45.133
Reputation: 219
1
You're stuck in a bit of a bad situation here... The first rule of names is there are no rules for names. That said, your best bet might be to drop into VBA and use intermediate variables rather than these crazy long formulae.
– Bob – 2017-01-11T05:30:02.003My VBA knowledge is only as good as my knowledge in nuclear astrophysics or in Swahili.!!! :-( Highly appreciate any help. – CRoshanLG – 2017-01-11T05:45:02.570
1
Is it important that you run this in Excel and/or be able to run it multiple times? I mean, sure, it's possible - and someone else may have an answer for you - but it's also not my cup of tea ;) if alternative solutions are acceptable then I can try to help - just drop into chat and ping me. Anyway, the first thing you can do is get a list of all possible salutations and pray that they never turn up in actual first names.
– Bob – 2017-01-11T05:50:10.773I'm open to alternatives, as long as thery get the job done. Already have a list of salutations and pretty sure they don't occur in proper names. – CRoshanLG – 2017-01-11T05:59:25.217
You said: "Mohomad Hussain Mohomad Niyas" needs to be changed into "M. H. M. Niyas". I think the correct form would be "M. H. Mohomad Niyas". It looks like a compound family name to me. – None – 2017-01-11T06:17:41.253
Well, in that specific instance, you might be correct. But I just wanted to convey that there are names with four parts, in a generic sense. :-) Thanks for the reply. – CRoshanLG – 2017-01-11T06:25:55.213
Here's an example: https://ideone.com/hKmxrp -- click edit up the top left, add your salutations to the code (keeping in mind that this assumes only one salutation for any name, no "Dr Dr Smith") and paste your list of names into the input textbox below. I'm sure it's possible to translate into VBA or an Excel formula -- e.g. in a formula you could use a vlookup to check salutations against a list in a different column. Anyway, this is a bit much for the comments, so better if you hop into chat - and it's not really a proper answer for the question as asked.
– Bob – 2017-01-11T06:26:05.780@FleetCommand That would fall under "no rules for names", maybe better stated as "no universal rules for names". When dealing with messy data like this, something will get mangled :\ -- honestly, you're probably better off either using it as-is or asking the users to input in the initial-last format. – Bob – 2017-01-11T06:26:55.210