Text Split or any other formula

[ChetanPuri]

Good Morning Excel Gurus,

Is there a formula, that I am able split data in the below format for example Value in P2 if its in format R2402/104/PR, then it splits in T2, U 2 and V2 , even though I put the formula in Col;umn S2 as I want any number, for example 40719240 to appear in S2 and if its not a number or is it with Delimiter should be split in column T,U and V. Any help would be appreciated

V4 Telstra Download 2024-11-21.xlsm
	P	Q	R	S	T	U	V
1	GL Code	SPLIT			Tex Split
2	R2402/104/PR	53			R2402	104	PR
3	D100/130/MT	33			D100	130	MT
4	D100/130/MT	66			D100	130	MT
5	D100/160/MT	66			D100	160	MT
6	D100/160/MT	61			D100	160	MT
7	D100/140/MT	33			D100	140	MT
8	C241/0800/PR	53			C241	0800	PR
9	C241/0800/PR	53			C241	0800	PR
10	C242/0800/PR	14.5			C242	0800	PR
11	C241/0800/PR	14.5			C241	0800	PR
12	C241/0800/PR	53			C241	0800	PR
13	C241/0800/PR	50			C241	0800	PR
14	C241/0800/PR	62.99			C241	0800	PR
15	40719240	53		40719240
16	10119240	70		10119240
17	11229392	354.99		11229392
18	10119240	70		10119240
19	40719240	135		40719240
20	40739710	165		40739710
21	DN04B/0800/PR	85		DN04B	0800	PR
Detail Report

Cell Formulas
Range	Formula
T15:T21	T15	=IFERROR(MID(P15,FIND("/",P15)+1,FIND("/",P15,FIND("/",P15)+1)-FIND("/",P15)-1),"")
U15:U21	U15	=IFERROR(MID(P15,FIND("/",P15,FIND("/",P15)+1)+1,LEN(P15)),"")
Q2:Q21	Q2	=O2*A2
S21	S21	=IFERROR(VALUE(P21),MID(P21,1,FIND("/",P21&"/")-1))

 

[Phuoc]

Try this:

Book1
	P	Q	R	S	T	U	V
1	GL Code	SPLIT			Tex Split
2	R2402/104/PR	0			R2402	104	PR
3	D100/130/MT	0			D100	130	MT
4	D100/130/MT	0			D100	130	MT
5	D100/160/MT	0			D100	160	MT
6	D100/160/MT	0			D100	160	MT
7	D100/140/MT	0			D100	140	MT
8	C241/0800/PR	0			C241	0800	PR
9	C241/0800/PR	0			C241	0800	PR
10	C242/0800/PR	0			C242	0800	PR
11	C241/0800/PR	0			C241	0800	PR
12	C241/0800/PR	0			C241	0800	PR
13	C241/0800/PR	0			C241	0800	PR
14	C241/0800/PR	0			C241	0800	PR
15	40719240	0		40719240
16	10119240	0		10119240
17	11229392	0		11229392
18	10119240	0		10119240
19	40719240	0		40719240
20	40739710	0		40739710
21	DN04B/0800/PR	0			DN04B	0800	PR
Sheet1

Cell Formulas
Range	Formula
Q2:Q21	Q2	=O2*A2
S21:V21,S15:S20,S2:V14	S2	=IF(ISNUMBER(P2),P2,TEXTSPLIT("/"&P2,"/"))
Dynamic array formulas.

 

[Scott Huish]

Another way:
=DROP(TEXTSPLIT("/"&P2,"/"),,--ISNUMBER(P2))

Book1
	P	Q	R	S	T	U	V
1	GL Code	SPLIT			Tex Split
2	R2402/104/PR	0			R2402	104	PR
3	D100/130/MT	0			D100	130	MT
4	D100/130/MT	0			D100	130	MT
5	D100/160/MT	0			D100	160	MT
6	D100/160/MT	0			D100	160	MT
7	D100/140/MT	0			D100	140	MT
8	C241/0800/PR	0			C241	0800	PR
9	C241/0800/PR	0			C241	0800	PR
10	C242/0800/PR	0			C242	0800	PR
11	C241/0800/PR	0			C241	0800	PR
12	C241/0800/PR	0			C241	0800	PR
13	C241/0800/PR	0			C241	0800	PR
14	C241/0800/PR	0			C241	0800	PR
15	40719240	0		40719240
16	10119240	0		10119240
17	11229392	0		11229392
18	10119240	0		10119240
19	40719240	0		40719240
20	40739710	0		40739710
21	DN04B/0800/PR	0			DN04B	0800	PR
Sheet1

Cell Formulas
Range	Formula
Q2:Q21	Q2	=O2*A2
S21:V21,S15:S20,S2:V14	S2	=DROP(TEXTSPLIT("/"&P2,"/"),,--ISNUMBER(P2))
Dynamic array formulas.

 

[Cubist]

Without the DROP

=TEXTSPLIT("/"&P2,"/",,ISNUMBER(P2))

 

[ChetanPuri]

Without the DROP

=TEXTSPLIT("/"&P2,"/",,ISNUMBER(P2))

Thank you , never though this code be achieved , you are a legend

 

[Cubist]

You're welcome. I simply built on the prior answers so can't take all the credit.

 

[Rick Rothstein]

If I am not mistaken, this single formula should generate all four of the columns you want...

=LET(t,TEXTSPLIT(TEXTAFTER("/"&P2:P195,"/",{1,2,3}),"/",,1),HSTACK(TAKE(IF(ISERROR(TAKE(t,,-1)),t,""),,1),IF(ISNUMBER(-TAKE(t,,1)),"",IF(NOT(ISERROR(t)),t,""))))

 

[ChetanPuri]

Thank You Rick much appreciated yes you are right was looking for a single formula

 

[Cubist]

Another way:

=IFNA(DROP(REDUCE("",P2:P195,LAMBDA(a,b,VSTACK(a,TEXTSPLIT("/"&b,"/",,ISNUMBER(b))))),1),"")