Splitting cell text into multiple columns without using text to columns

[jimbogarner]

Hi,

Is there a way to split text to columns without using that specific function? The reason I ask is I have a load of data that is split by a ' - ', I was originally planning to set up a macro that organises and splits the data into columns using the '-' delimiter (as well as a load of other things), however, we're now pulling in dates that are formatted as dd-mm-yyyy. Basically, I need it to split into a column when there is a space before the '-'... it also has issues if someone has a hyphenated name...

Data comes out like the following;
Attend - 20-09-2022 - Jo Bloggs - Cat A
Attend - 20-09-2022 - Jim Bloggs - Cat A
Attend - 21-09-2022 - Jim-Bean Bloggs - Cat B
Etc. Etc.

Thanks,

James

 

[jtakw]

Hi,

Would this work for you:

Book3.xlsx
	A	B	C	D	E
1	Attend - 20-09-2022 - Jo Bloggs - Cat A	Attend	20-09-2022	Jo Bloggs	Cat A
2	Attend - 20-09-2022 - Jim Bloggs - Cat A	Attend	20-09-2022	Jim Bloggs	Cat A
3	Attend - 21-09-2022 - Jim-Bean Bloggs - Cat B	Attend	21-09-2022	Jim-Bean Bloggs	Cat B
Sheet987

Cell Formulas
Range	Formula
B1:E3	B1	=TRIM(MID(SUBSTITUTE($A1," - ",REPT(" ",100)),COLUMNS($B1:B1)*100-99,100))

 

[jimbogarner]

Perfect thank you so much!

 

[jtakw]

You're welcome, thanks for the feedback.