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...
See if the attached workbook does what you want. I used some helper columns.
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!
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.
- Not every name has a middle name but if they do it is in this format (w/o the point):
Lopez, Juan FSee if the attached workbook does what you want. I used some helper columns.