Trouble with Wild Cards

[Joe Stephens]

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

 

[AhoyNC]

As far as the problem with USE and FUSE, instead of the wild card, put a space before and after USE - " USE ". You will also need to add a space before and after the 2nd argument in the SEARCH function.
See examples below.
Hopefully someone else can help with your second issue.

Excel Workbook
	A	B
1	USE 467.209	467.209
2	FUSE 467.209
Sheet

 

[Aladin Akyurek]

Also a space in front of A1, even better:

" USE " against " "&A1&" ".

 

[Joe Stephens]

As far as the problem with USE and FUSE, instead of the wild card, put a space before and after USE - " USE ". You will also need to add a space before and after the 2nd argument in the SEARCH function.
See examples below.
Hopefully someone else can help with your second issue.

*	A	B
1	USE 467.209	467.209
2	FUSE 467.209	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:273px;"><col style="width:83px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =IF(ISNUMBER(SEARCH(" USE "," "&A1&" ")-1),TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),"") B2 =IF(ISNUMBER(SEARCH(" USE "," "&A2&" ")-1),TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2))),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanx for your prompt response to this request! As it turns out there are additional issues with the data - and this really helps to identify them, i.e., -NEW, "NEW ", etc.
Hopefully, someone will respond with a solution to the following text.

Joe

 

[Joe Stephens]

 

[Joe Stephens]

Also a space in front of A1, even better:

" USE " against " "&A1&" ".

Yes, this worked very well - Thanx!

Joe