Trim Function

[kipper19]

Hi all, I have the following data in Column A, I would to trim the data after the wording R1 or R2 or R3 etc etc and show it in column E, formula shown does work if the data in Colum A shows R1, how can i incorporate to show the trimmed data if the wording is not R1 and maybe R5

 

[Muhammad_Usman]

Just Drag /your Formula to right .

only lock Cell A3 = A$3$

 

[Muhammad_Usman]

1235.xlsm
	A	B	C	D
1		R1	R2	R3
2	10-Jube-23 1300m Hwavy 8 R1 RTG66+	RTG66+
3	10-Jube-23 1300m Hwavy 8 R2 RTG66+		RTG66+
4	10-Jube-23 1300m Hwavy 8 R3 RTG66+			RTG66+
Sheet4

Cell Formulas
Range	Formula
B2:D4	B2	=IFERROR(TRIM(MID($A2, SEARCH(B$1, $A2) + 2, LEN($A2))),"")

 

[Fluff]

How about

=TRIM(TEXTAFTER(A3,"R"&SEQUENCE(,9)))

 

[kipper19]

1235.xlsm
	A	B	C	D
1		R1	R2	R3
2	10-Jube-23 1300m Hwavy 8 R1 RTG66+	RTG66+
3	10-Jube-23 1300m Hwavy 8 R2 RTG66+		RTG66+
4	10-Jube-23 1300m Hwavy 8 R3 RTG66+			RTG66+
Sheet4

Cell Formulas
Range	Formula
B2:D4	B2	=IFERROR(TRIM(MID($A2, SEARCH(B$1, $A2) + 2, LEN($A2))),"")

1235.xlsm
	A	B	C	D
1		R1	R2	R3
2	10-Jube-23 1300m Hwavy 8 R1 RTG66+	RTG66+
3	10-Jube-23 1300m Hwavy 8 R2 RTG66+		RTG66+
4	10-Jube-23 1300m Hwavy 8 R3 RTG66+			RTG66+
Sheet4

Cell Formulas
Range	Formula
B2:D4	B2	=IFERROR(TRIM(MID($A2, SEARCH(B$1, $A2) + 2, LEN($A2))),"")

Muhammed, thanks for the reply, that would work but i need all of the results to show only in Column B

 

[kipper19]

How about

=TRIM(TEXTAFTER(A3,"R"&SEQUENCE(,9)))

Fluff, appreciate your time but my excel does not support the TextAfter function, using Office and Home 2019

 

[Fluff]

using Office and Home 2019

Then why does your profile show 365?

 

[kipper19]

Apologies, Im using this on my laptop with a different system, I should have stipulated which version in the original post

 

[PdeB]

Then this would be an alternative to using the textsplit:

=CONCAT(IFERROR(MID(A2,FIND("R"&ROW($1:$99),A2)+LEN(ROW($1:$99))+2,LEN(A2)),""))

 

[kipper19]

Then this would be an alternative to using the textsplit:

=CONCAT(IFERROR(MID(A2,FIND("R"&ROW($1:$99),A2)+LEN(ROW($1:$99))+2,LEN(A2)),""))

Thanks PdeB