Excel: Splitting a column with different types of data

an Excel split column questions for you all.

Let's say I need to split all cells in column A.

Each cell contains multiple bits of spatial data, but doesn't always contain the same value ranges. Example:

A1: FS13, 1000N, 10-20CM
B1: 1000N, 20-30
C1: FS18, 1017N, LVL2, 20-30CM

I would need new columns for FS, N, LVL, and CM. But as you'll notice, not each cell contains all spatial data, and some cells have that information out of order relative to other cells in the column. Is there a way to split AS WELL AS sort the data into the proper order for the new columns?

Solution 1:

Create a header row that shows the four possibilities in the order desired. The use CONCAT in an array formula:

=CONCAT(IF(ISNUMBER(SEARCH(B$1,TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",999)),(ROW($1:$4)-1)*999+1,999)))),TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",999)),(ROW($1:$4)-1)*999+1,999)),""))

Being an array formula one must confirm with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",999)),(ROW($1:$4)-1)*999+1,999)) is the part that iterates through the string breaking it into its parts based on the ,.

The we Search for the title in each of those parts and if it is there we return it to the CONCAT or a null string.

Once the new formula are released from microsoft we can replace the (ROW($1:$4)-1) parts with SEQUENCE(4,,0) and the need to array enter is no longer needed.