working with strings

[kraddark]

in cell A1= RAMOS, HENRY MARK III DE LEON

result should be:

B1= RAMOS (no comma last name)
C1= HENRY MARK (first name)
D1= DE LEON (middle name)
E1= III (suffix)

in cell A2= DE LEON, JOHN JR. HENRY

result should be:

B2= DE LEON (no comma last name)
C2= JOHN (first name)
D2= HENRY (middle name)
E2= JR (suffix)



How do I solve these? thanks!

 

[Peter_SSs]

Tricky problem, because Excel can only work with a set of rules (formulas or code) that it is given. Do you have say 10 representative examples and results and could you write, in English, a set of rules that describes how to determine what should go in each column?

Seems like Last name is easy: Whatever comes before the comma.
However, the other 3 columns may not be easy. If you can't write a set of rules in English, then it will be hard to write a set of formulas or code to do the job.

Does every name have a suffix? If not, how do you tell which ones do and which ones don't?

For example
Smith, Peter John Paul
Is the first name Pater John or just Peter?
Is the middle Name just Paul or John Paul

Smith, Peter Junior Paul
How would you tell if Junior is a suffix or an actual first name or middle name?

Smith, Simon I
How do you tell if the I is a suffix or a middle name initial?

 

[Hashiru]

Hi to all.

I think there could be a solution through delimiter. For this to work in all cases the questionnaire needs to state what rules to observe in these columns.

 

[kraddark]

Tricky problem, because Excel can only work with a set of rules (formulas or code) that it is given. Do you have say 10 representative examples and results and could you write, in English, a set of rules that describes how to determine what should go in each column?

Seems like Last name is easy: Whatever comes before the comma.
However, the other 3 columns may not be easy. If you can't write a set of rules in English, then it will be hard to write a set of formulas or code to do the job.

Does every name have a suffix? If not, how do you tell which ones do and which ones don't?

For example
Smith, Peter John Paul
Is the first name Pater John or just Peter?
Is the middle Name just Paul or John Paul

Smith, Peter Junior Paul
How would you tell if Junior is a suffix or an actual first name or middle name?

Smith, Simon I
How do you tell if the I is a suffix or a middle name initial?

Thanks for the reply. The format of every name on my list is Lastname, Firstname, Suffix (if there's any which is Jr., I, II, III and not spelled completely), MiddleName (not Middle Initial).. Can you help me extract the values needed in each column? thanks again.

 

[Peter_SSs]

Can you help me extract the values needed in each column?

I don't know. I asked a number of questions to help me evaluate whether I could assist or not, but you didn't answer all of them.

The format of every name on my list is Lastname, Firstname, Suffix (if there's any which is Jr., I, II, III and not spelled completely), MiddleName (not Middle Initial).. thanks again.

I that a complete list of possible Suffixes?
If not, do you have a complete list?

You say "Suffix, if there's any". That implies that there may not be any suffix. So let's take your first example and assume there was no suffix in this case.

RAMOS, HENRY MARK DE LEON
How would we know which of the following was correct?

First: HENRY
Middle: MARK DE LEON

or

First: HENRY MARK
Middle: DE LEON

or

First: HENRY MARK DE
Middle: LEON

Comes back to the fact that Excel cannot view the text like a human can, it can only follow a strict set of rules we give it and that is why I asked the various questions in my first post as well as this one. You would need to go back and address each of the questions/requests that I made for us to decide if help might be possible.