Splitting text string in excel using a formula

Question

I am trying to extract text in a string from an excel cell using a formula. The string is separated by " - ". The data comes from a data source I can't adjust:

Cell A1 (Original) The cat sat on the mat - the mat chat to the rat - the rat spat on the cat - no more cat

My desired outcome is having 4 formulas splitting the string into the four parts. (1 for each section)

Cell A2 (First part) The cat sat on the mat

Cell A3 (Second part) the mat chat to the rat

Cell A4 (Third part) the rat spat on the cat

Cell A5 (Fourth part) no more cat

@Jeeped: I do not agree that this is a duplicate! I have a formula i want to submit as an answer, so please reopen this question. — Marco Vos, May 10 '16 at 07:47
@MarcoVos - This has been asked and answered hundreds of times. The question I pointed to as a duplicate is only one of dozens of similar questions I have offered a solution to. The answers being offered are general computing knowledge and the question is crappy to start with. If you can convince the OP to show any original effort at all toward a solution, I'll be happy to reopen it. — , May 10 '16 at 07:52
@Jeeped - In my opinion this question is not about extracting a piece of text out of a string, but about transposing Text to Rows. I created a formula that does just this and i would like to submit it as an answer. So OP, if your interested, you know what to do...... — Marco Vos, May 10 '16 at 08:06
Hi @MarcoVos, your help would be appreciated. The suggested solution from a previous question as outlined by Jeeped doesn't help me. Perhaps Jeeped would refer me to the other answers to this question or consider reopening or helping me with this. With respect to the deliniter solution below, will this work on a live connection which is regularly updated where the rows increase over time? — Benjamin Leschke, May 10 '16 at 09:27
@BenjaminLeschke You should be able to easily adapt the splitting portion of [the accepted answer of this question](http://stackoverflow.com/questions/25316094/split-a-string-cell-in-excel-without-vba-e-g-for-array-formula) by wrapping the formula with the `INDEX` to get your results. — Ron Rosenfeld, May 10 '16 at 11:57
Hi @BenjaminLeschke -- : =TRIM(MID(SUBSTITUTE(A$1,"-",REPT(" ",999)),ROWS($1:1)*999-998,999)) Put that in cell A2 and copy down. — Marco Vos, May 10 '16 at 13:25
@MarcoVos - The other option is to write a good question yourself and self-answer it. Self-answered questions are not expected to show original effort as much as a user's question. I have done this on a rare occasion when I have an answer sitting in draft for an extended period waiting for a response and the OP has abandoned their question. — , May 10 '16 at 16:39
@MarcoVos - Of course, there is [this](http://stackoverflow.com/questions/34126367#34126367) and [this](http://stackoverflow.com/questions/32337802#32337802) and [this](http://stackoverflow.com/questions/26704938#26704938) and [this](http://stackoverflow.com/questions/32864814#32864814) and [this](http://stackoverflow.com/questions/32646134#32646134) and [this](http://stackoverflow.com/questions/32348089#32348089) and [this](http://stackoverflow.com/questions/32663855#32663855) and [this](http://stackoverflow.com/questions/36036995#36036995), etc that may be construed as duplicates by someone. — , May 10 '16 at 16:50

score 1 · Answer 1 · answered May 10 '16 at 06:07

Select the cell to be delimited.
Go to Data tab and choose Text to Columns button.
Choose the radio "delimiter" and Click Next.
Click "other" and type -.

This will give you delimited data in different columns. Now, I suppose you need the data in different rows. Copy the four cells and paste special (transpose) them at the required cells.

score -1 · Answer 2 · answered May 10 '16 at 06:14

Go to Data > Text to Columns, and the wizard will walk you through the process.

Select the cell or column that contains the text you want to split.
Click Data >Text to Columns.
Then just go with the wizaard :

Select the cells where you want to paste your split cells and the split will be done.

Source

Splitting text string in excel using a formula

2 Answers2