Extract Number fron Cell

[RobNSB]

I am looking to extract a number form a cell as per the below examples:

The issue i have is the number of digits change. So I would be looking to extract the number between the last "x" and "ub" so in the first instance i would be looking to extract "147" and in the second instance I would be looking to extract "60".

Please help as I am going around in circles.

762×267×147 UB

406×178×60 UB

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B
1
2	762×267×147 UB	147
3	406×178×60 UB	60
Results

Cell Formulas
Range	Formula
B2:B3	B2	=TRIM(RIGHT(SUBSTITUTE(REPLACE(A2,LEN(A2)-2,3,""),CHAR(215),REPT(" ",100)),100))+0

 

[RobNSB]

How about

+Fluff 1.xlsm
	A	B
1
2	762×267×147 UB	147
3	406×178×60 UB	60
Results

Cell Formulas
Range	Formula
B2:B3	B2	=TRIM(RIGHT(SUBSTITUTE(REPLACE(A2,LEN(A2)-2,3,""),CHAR(215),REPT(" ",100)),100))+0

Absolute genius, thank you very much!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option might be (allowing for up to 5 digits in that final number but can be extended):

21 04 05.xlsm
	A	B
1
2	762×267×147 UB	147
3	406×178×60 UB	60
Extract Number

Cell Formulas
Range	Formula
B2:B3	B2	=AGGREGATE(14,6,RIGHT(LEFT(A2,LEN(A2)-3),{1,2,3,4,5})+0,1)

 

[jtakw]

Hi,

Just another way:

Book3.xlsx
	A	B
1	762×267×147 UB	147
2	406×178×60 UB	60
Sheet894

Cell Formulas
Range	Formula
B1:B2	B1	=RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-3),CHAR(215),REPT(" ",30)),30)+0

 

[RobNSB]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option might be (allowing for up to 5 digits in that final number but can be extended):

21 04 05.xlsm
	A	B
1
2	762×267×147 UB	147
3	406×178×60 UB	60
Extract Number

Cell Formulas
Range	Formula
B2:B3	B2	=AGGREGATE(14,6,RIGHT(LEFT(A2,LEN(A2)-3),{1,2,3,4,5})+0,1)

Thank you, your help is very much appreciated.

 

[RobNSB]

Hi,

Just another way:

Book3.xlsx
	A	B
1	762×267×147 UB	147
2	406×178×60 UB	60
Sheet894

Cell Formulas
Range	Formula
B1:B2	B1	=RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-3),CHAR(215),REPT(" ",30)),30)+0

Thank you, your help is very much appreciated.

 

[jtakw]

You're welcome, thanks for the feedback.