Arrange names in cell

Dear All,

I have data like

SINGH, RAVI B. & PAYAL S.

I want to convert like below

Ravi & Payal Singh


in some cases like below

RAVI & PAYAL SINGH LLC

convert like below

Ravi & Payal Singh LLC

Many thanks in advance if this is possible need your support.

1. I do not believe that 2 examples cover ALL of the possibilities that you have. Please prepare a SMALL REPRESENTATIVE sample tht contains ONE of all the punctuation/order/ Initials possibilities that you have to deal with and re-post. Otherwise, you waste a lot of time looking at replies that address only PART of the problem.

2. Please confirm that you are still using Excel 2007, or update your profile.

2007 still using

Originally Posted by Glenn Kennedy

1. I do not believe that 2 examples cover ALL of the possibilities that you have. Please prepare a SMALL REPRESENTATIVE sample tht contains ONE of all the punctuation/order/ Initials possibilities that you have to deal with and re-post. Otherwise, you waste a lot of time looking at replies that address only PART of the problem.

2. Please confirm that you are still using Excel 2007, or update your profile.

all the punctuation/order/ Initials are like attached file.

I just download the sheet but my excel is not capable to open this file why?

Can we have a COMPLETE list of initials that remain capitalised, (e.g. LLC)?

Are there ANY instances where someone has TWO initials? if so, how are they punctuated (with/without spaces, with/without full stops)

Are there any instances of personal suffixes (Jr, II, etc)? If so, a complete list and desired capitalisation, please.

Are there any instances where and is used instead of the ampersand?

1- the list contains only LLC in case of company name that need to keep capitalized.
2- We have only one initials (like I attached)
3- No suffix like (Jr, II, or any)
4- not at all only ampersand are there.

Well... I think you would be MUCH better off NOT using a formula. VBA, perhaps.

Sometimes when you start down a road, you KNOW you are getting completely LOST, but it's too late to turn back... and you just keep going to see where you wind up...

This is the WORST formula I have created in YEARS and YEARS. It makes me laugh... just to look at it...

If there ARE exceptions that you STILL haven't told us about... i do not want to know. There is nO WAY that I am going to unpick this thing.

Formula:

Please Login or Register  to view this content.

ANYTHING has to be better than this....

Originally Posted by Glenn Kennedy

Well... I think you would be MUCH better off NOT using a formula. VBA, perhaps.

Sometimes when you start down a road, you KNOW you are getting completely LOST, but it's too late to turn back... and you just keep going to see where you wind up...

This is the WORST formula I have created in YEARS and YEARS. It makes me laugh... just to look at it...

If there ARE exceptions that you STILL haven't told us about... i do not want to know. There is nO WAY that I am going to unpick this thing.

Formula:

Please Login or Register  to view this content.

ANYTHING has to be better than this....

thank you thank you so much Glenn, you are real hero for me its working now.

let me work with this.

again many thanks for your time and solution.

Continued here: https://www.excelforum.com/excel-for...h-formula.html