SOLVED

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

Copper Contributor

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

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

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!

8 Replies

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

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))

Thank you!

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

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!

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

Will every name have a middle initial?

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

Lopez, Juan F.

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

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

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

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)

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

Regards