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

 

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

Not sure if it requires being entered with Ctrl+shift+enter, but I think it does. (Unable to test this in my Excel version)

 

[PdeB]

I didn't test my previous answer well enough. For it to work properly for numbers from 10 and up it would return two strings (for both finding R1 and R10), therefore we need a slight alteration:

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

 

[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.

 

[PdeB]

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.

The idea of my formula is to not be using the header cells, but this is indeed a perfect solution if you are to use them.

My version will indeed change the row references when entire rows are deleted or added, and especially if a row is added prior to the first row it would break the functionality. I still think it's a good solution if you keep that in mind and you want to avoid the use of helper headers.

 

[Peter_SSs]

I still think it's a good solution if you keep that in mind and you want to avoid the use of helper headers.

I didn't consider them as 'helper' headers since the OP already had 'R' values in row 1
If they were to be omitted/avoided it could be done like this without the potential row insertion problem.

23 10 22.xlsm
	A		C
1
2	10-Jun-23 1300m Hwavy 8 R7 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 R6 def		def
kipper19 (2)

Cell Formulas
Range	Formula
C2:C7	C2	=IFNA(REPLACE(A2,1,FIND(" ",A2,LOOKUP(9^9,FIND("R"&ROW(INDIRECT("1:9")),A2))),""),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[PdeB]

I didn't consider them as 'helper' headers since the OP already had 'R' values in row 1
If they were to be omitted/avoided it could be done like this without the potential row insertion problem.

23 10 22.xlsm
	A		C
1
2	10-Jun-23 1300m Hwavy 8 R7 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 R6 def		def
kipper19 (2)

Cell Formulas
Range	Formula
C2:C7	C2	=IFNA(REPLACE(A2,1,FIND(" ",A2,LOOKUP(9^9,FIND("R"&ROW(INDIRECT("1:9")),A2))),""),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

True, but this would make it volatile. Good option to avoid the risk, but if we are aware of the risk I think it's better to avoid a volatile solution.
I think your reference to header cells would be preferable.

 

[Peter_SSs]

True, but this would make it volatile.

True. Not a problem though unless a lot of these volatile functions are used. However, if that was likely to be the case, easy to make it non-volatile.

23 10 22.xlsm
	A		C
1
2	10-Jun-23 1300m Hwavy 8 R7 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 R6 def		def
kipper19 (2)

Cell Formulas
Range	Formula
C2:C7	C2	=IFNA(REPLACE(A2,1,FIND(" ",A2,LOOKUP(9^9,FIND("R"&ROW(INDEX(A:A,1):INDEX(A:A,9)),A2))),""),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

I think your reference to header cells would be preferable.

At this stage I think it is preferable anyway since the OP's sample gave us headings - and the method I suggested using the headings would also work* even if the headings were not a neat sequence like their current example

* provided heading text does not repeat in a cell & provided there is a space character after the heading text as per the OP's samples.