Joe Stephens
New Member
- Joined
- Jul 22, 2016
- Messages
- 4
Hello all -
Here's where I am stuck:
When the word 'NEW" appears at any place in a row, I want to retrieve the part number located a single space away, i.e., USE 467.209 returns 467.209.
And there are instances where additional text follows the number (USE 55-1235OE,2 LEAF AIR BEAM L/H 1/SB/1) - which is not wanted.
The formula does not work consistently and it appears the issue is Excel is not distinguishing the difference between "USE" and "FUSE" (returning undesirable results) even when inserting the wild card * ("*USE") in the following formula:
=IF(ISNUMBER(SEARCH("*USE",B3400)-1),TRIM(RIGHT(SUBSTITUTE(B3400," ",REPT(" ",LEN(B3400))),LEN(B3400))),"").
Any thoughts?

Thanx!
Joe
Here's where I am stuck:
When the word 'NEW" appears at any place in a row, I want to retrieve the part number located a single space away, i.e., USE 467.209 returns 467.209.
And there are instances where additional text follows the number (USE 55-1235OE,2 LEAF AIR BEAM L/H 1/SB/1) - which is not wanted.
The formula does not work consistently and it appears the issue is Excel is not distinguishing the difference between "USE" and "FUSE" (returning undesirable results) even when inserting the wild card * ("*USE") in the following formula:
=IF(ISNUMBER(SEARCH("*USE",B3400)-1),TRIM(RIGHT(SUBSTITUTE(B3400," ",REPT(" ",LEN(B3400))),LEN(B3400))),"").
Any thoughts?

Thanx!
Joe