Forum Discussion
Solved
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
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!
See if the attached workbook does what you want. I used some helper columns.
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)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))
- 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!Will every name have a middle initial?
And does a middle initial always have a point after it? For example
Lopez, Juan F.
- Thank you!
