extracting text from a string basis multiple delimiters

[ali1985]

Hi Experts,

I need a combined formula to extract the 3 text (date, month & year) from a string. the delimiters could be space, comma, - and /. can we manage this in one single formula?

I've listed down few examples below.....the date would be entered in cell A1 and basis the delimiters, the 3 output should be updated in 3 columns

Inputs Outputs that i need in 3 columns

Output in col B Output in col C output in col D
03.05.22 03 05 22
22/04/2022 22 04 2022
06-23-2021 06 23 2021
2022 04 01 2022 04 01
2022-Sep.01 2022 Sep 01

 

[Peter_SSs]

Your samples would be much easier to understand/copy/use if you provided them with XL2BB

Those outputs look strange (order)to me but this seems to produce them

22 10 06.xlsm
	A	B	C	D
1	03.05.22	03	05	22
2	22/4/2022	22	4	2022
3	06-23-2021	06	23	2021
4	2022 04 01	2022	04	01
5	2022-Sep.01	2022	Sep	01
Sheet2

Cell Formulas
Range	Formula
B1:D5	B1	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&$A1,"."," "),"/"," "),"-"," ")," ",REPT(" ",20)),COLUMNS($B:B)*20,20))

 

[ali1985]

Your samples would be much easier to understand/copy/use if you provided them with XL2BB

Those outputs look strange (order)to me but this seems to produce them

22 10 06.xlsm
	A	B	C	D
1	03.05.22	03	05	22
2	22/4/2022	22	4	2022
3	06-23-2021	06	23	2021
4	2022 04 01	2022	04	01
5	2022-Sep.01	2022	Sep	01
Sheet2

Cell Formulas
Range	Formula
B1:D5	B1	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&$A1,"."," "),"/"," "),"-"," ")," ",REPT(" ",20)),COLUMNS($B:B)*20,20))

This is perfect. Thank you so much

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.