Splitting a cell at a certian character

**kelemvor** · 03-19-2013, 12:42 PM

So, I have some cells that contain data like this:
ABCDE: @ CT 8:59 1)Text Data One 2)Text Data Two 3)Text Data Three.

I want to split up the cell so that the each set of data is in its own column.

I thought I could just use Text to Columns on the ")" sign but some of the data could actually contain parenthesis.

So I need a way that it can actually look for "1)" or "2)" or "3)" and do the split at that set of characters.

Can anyone assist me with this?

Thanks!

**Pepe Le Mokko** · 03-19-2013, 01:04 PM

As you are in the VBA forum, do you need a VBA solution?

**jindon** · 03-19-2013, 01:33 PM

Select the Cell(s) and run the code

Please Login or Register  to view this content.

**Marcol** · 03-19-2013, 01:36 PM

....but some of the data could actually contain parenthesis.

Then best you post a workbook with a sample of your data, and any variations.

This type of question invariably leads to ... but what if my string has ..... ?

**stanleydgromjr** · 03-19-2013, 02:36 PM

kelemvor,

I see that jindon has done it again. Nice - I have to learn how to use patterns.

With your raw data in the active sheet, beginning in cell A1.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the SplitOnClosedP macro.