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

 

[Peter_SSs]

I didn't test my previous answer well enough.

I would suggest that all the formula that you have suggested are a bit 'risky' in that if subsequently any rows are added at the top of the sheet, they would return incorrect results.

@kipper19
Here is another suggestion that you could try in your 2019 version.
If you are still not sure that you have a satisfactory answer from the various suggestions offered, please provide a larger set (say 8-10 rows) of varied sample data and the expected results for that data, preferably with XL2BB so that helpers can easily copy to test with that data.

23 10 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1					R1	R2	R3	R4	R5	R6	R7	R8	R9	R10	R11	R12
2	10-Jun-23 1300m Hwavy 8 R11 RTG 66+ $2	RTG 66+ $2
3	Hwavy 8 R2 RTG 67	RTG 67
4	10-Jun-23 1300m Hwavy 8 R3 RTG 68+ $100	RTG 68+ $100
5
6	abc
7	abc R12 def	def
kipper19

Cell Formulas
Range	Formula
B2:B7	B2	=IFNA(REPLACE(A2,1,FIND(" ",A2,LOOKUP(9^9,FIND(E$1:P$1,A2))),""),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[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)),""))