I have a data set that includes bunch of single cells that has multiple words with separator ";". Is there a way to separate this single cell into 3 different cells?
I am trying to automate this process so it is better to do it without excel's text-to-column function
If you are using an older version of Excel, like Office 2010 you can use;
=TRIM(MID(SUBSTITUTE($A2;";";REPT(" ";LEN($A2)));(COLUMNS($A:A)-1)*LEN($A2)+1;LEN($A2)))
Enter the formula and drag right. Depending on your locale settings you may need to change ";" with ","