Extracting a Number

[oscark37]

I've tried a couple things, but can't get a formula to successfully extract the number (6-10) in each cell.

Any ideas?

Thanks,

Oscar

[TABLE="width: 500"]
<tbody>[TR]
[TD]PERS BSTNE SIM/MOD RING GSS 8 HEA[/TD]
[/TR]
[TR]
[TD]PERS BSTNE SIM/MOD RING SS 10 HLO[/TD]
[/TR]
[TR]
[TD]PERS BSTNE SIM/MOD RING GSS 9 HEA[/TD]
[/TR]
[TR]
[TD]PERS BSTNE SIM/MOD RING SS 6 HEA[/TD]
[/TR]
[TR]
[TD]PERS BSTNE SIM/MOD RING SS 10 HEA[/TD]
[/TR]
[TR]
[TD]PERS BSTNE SIM/MOD RING SS 10 HEA[/TD]
[/TR]
</tbody>[/TABLE]

 

[Weazel]

maybe something like...

=0+TRIM(LEFT(RIGHT(A1,6),2))

 

[oscark37]

Thanks, but that is giving me #VALUE !

maybe something like...

=0+TRIM(LEFT(RIGHT(A1,6),2))

 

[Eric W]

Are there any other numbers possible in your data? Is the format of your data always the same, e.g. starting with the number you have a number, a space, and 3 characters?

If there are no other numbers in the data, this would work:

=LOOKUP(2^16,FIND({6,7,8,9,10},A1),{6,7,8,9,10})

 

[oscark37]

Genius! Thank you!

Are there any other numbers possible in your data? Is the format of your data always the same, e.g. starting with the number you have a number, a space, and 3 characters?

If there are no other numbers in the data, this would work:

=LOOKUP(2^16,FIND({6,7,8,9,10},A1),{6,7,8,9,10})

 

[Eric W]

Glad we could help!