Need to break up 1 cell into multiple columns (text has multiple conditions)

[marshalltj67]

Need to break up 1 cell into multiple columns (text has multiple conditions)

The source cell is as follows:

Last, First, MI Rank USSF MAJCOM 123 SQUADRON/OFFICE

Rank conditions are as follows:

AB

Amn

A1C

SrA

SSgt

TSgt

MSgt

SMSgt

CMSgt

2d Lt

1st Lt

Capt

Maj

Lt Col

Col

I am trying to generate a column for the following:

Column 1: Last, First, MI

Column 2: Rank

Column 3: MAJCOM 123 SQUADRON/OFFICE 

I have been trying to use Left, Right, and Mid functions with Find and Search have had no luck finding the right formula for each column.

I am using Excel 2015 and can't update to the latest version.

Thanks!

[#2]

@marshalltj67 

With such a value in A2:

In B2:  =TRIM(LEFT(SUBSTITUTE(A2, " ", REPT(" ", 255)), 600))

In C2:  =TRIM(MID(SUBSTITUTE(A2, " ", REPT(" ", 255)), 600, 255))

In D2:  =TRIM(MID(SUBSTITUTE(A2, " ", REPT(" ", 255)), 1100, 1000))

[#3]

Thank you!

[#4]

Where do the values in the REPT function come from? I have some instances in the RANK column where "1st Lt" is outputting as "1st" and "Lt Col" outputting as "Lt". In addition, if the individual name is long it is outputting their middle initial.

Thanks for the help!

[#5]

@marshalltj67 

Will every name have a middle initial?

And does a middle initial always have a point after it? For example

Lopez, Juan F.

[#6]

Not every name has a middle name but if they do it is in this format (w/o the point):
Lopez, Juan F

[#7]

Certainly, here are the shorter versions of the formulas:

Column 1 (Last, First, MI):

=LEFT(A1, FIND(" ", A1) - 1)

Column 2 (Rank):

=MID(A1, FIND(" ", A1) + 1, FIND(" ", A1, FIND(" ", A1) + 1) - FIND(" ", A1) - 1)

Column 3 (MAJCOM 123 SQUADRON/OFFICE):

=MID(A1, FIND(" ", A1, FIND(" ", A1) + 1) + 1, LEN(A1) - FIND(" ", A1, FIND(" ", A1) + 1))

Regards

(external link removed by moderator)

[#8 · Accepted solution]

@marshalltj67 

See if the attached workbook does what you want. I used some helper columns.

[#9]

Thank you so much! Code worked perfectly!