microsoft excel
My excel column is filled with words like this:
1.) ABC:DCF
2.) DCF:FED
I want to split each word based on " : " and put the result in adjacent columns such that "ABC:DCF" in cell "A:1" becomes "ABC" in cell "B:1" and "DCF" in cell "C:1" and also corresponding values in each column. How to do this?
Use FIND-function to determine the position of your first and last space. After that, use theMID-function to split your data into three parts. If you know the position of your spaces, the splitting is easy. Finding the first space is also trivial since FIND goes from left to right. Unfortunatly, there is no way to tell Excel to search from right to left.
So the difficult part is, to determine the last space! Here we use an array formula.
To enter an array formula, you paste the formula into a cell and don't hit Enter.
Instead you press Ctrl+Shift+Enter.
{=MAX((MID(A2,ROW(A:A),1)=" ")*ROW(A:A))}
If done successfully, Excel will append a starting and ending curly bracket.
Insert all formulas at the given cells and use autofill down
A2: your data goes here
B2:=FIND(" ",A2,1)
C2:=MAX((MID(A2,ROW(A:A),1)=" ")*ROW(A:A)) <-array formula!
D2:=MID(A2,1,B2-1)
E2:=MID(A2,B2+1,C2-B2-1)
F2:=MID(A2,C2+1,LEN(A2))
Result screen
Best Answer
Go to Data tab, then Text to Columns option. Later, choose "Delimited" option and then select "other" and put any delimiter you want.