Search text and display required information

PRADEEPSWALSE

New Member
Hello,

I am attempting to use a formula that will search for key words in text fields of cells and return the appropriate category.
=LOOKUP(9.99999999999999E+307,SEARCH(" "&MOC&" "," "&$C2),CODE)

But it returns to #N/A.

PIPE SIZE, 20NB, SCH STD, ASTM A106 GR.B, PE, SEAMLESS, B 36.10M

<tbody>
</tbody>
#N/A

<tbody>
</tbody>
PIPE SIZE, 15NB, SCH STD, ASTM A312 TP304, PE, SEAMLESS, B 36.10M

<tbody>
</tbody>
#N/A

<tbody>
</tbody>
PIPE SIZE, 15NB, SCH XS, ASTM A333 GR.6, PE, SEAMLESS, B 36.10M

<tbody>
</tbody>
#N/A

<tbody>
</tbody>
PIPE SIZE, 15NB, SCH XXS, ASTM A106 GR.B, PE, SEAMLESS, B 36.10M

<tbody>
</tbody>
#N/A

<tbody>
</tbody>

<tbody>
</tbody>














MOC

<tbody>
</tbody>
CODE

<tbody>
</tbody>
ASTM A106 GR.B

<tbody>
</tbody>
C01
ASTM A333 GR.6

<tbody>
</tbody>
L01
ASTM A312 TP304

<tbody>
</tbody>
S01

<tbody>
</tbody>











Please help.
 

PRADEEPSWALSE

New Member
Hello Sir,

This is short word fixed by me for some material. I want use this short word (i.e. C01, L01 etc.) to other place.
If cell contains text "ASTM A106 GR.B" then in next cell shows "C01" and if cell contains text "ASTM A333 GR.6" then in next cell shows "L01" and so on... I have more than 100 cases.
 

PRADEEPSWALSE

New Member
Try

=LOOKUP(9.99999999999999E+307,SEARCH(MOC,$C2),CODE)

M.

Thanks for the solution. It is working fine and I getting desired results.

Can you please explain that how this formulae is working?

This will help me in future, if required.
 

Marcelo Branco

MrExcel MVP
You're welcome.

The formula i suggested above is almost identical to yours. The only difference is that it doesn't concatenate a space before and after MOC. Observe that in the long text the instances are followed by a comma, not a space - that's is the reason why your original formula wasn't working.

M.
 

PRADEEPSWALSE

New Member
Hello,

Okay.

Now I am facing another problem.


MOCCODE
ASTM A420 GR.WPL6L02
ASTM A420 GR.WPL6-WL03

<tbody>
</tbody>
In above table, MOC column contains different text with minor difference i.e "-W" is available in second row.

Refer below table, in first case result shows "#N/A" and for second case correct result is appearing.


90 DEG. LR ELBOW, SCH 80, BW, ASTM A420 GR.WPL6#N/A
90 DEG. LR ELBOW, SCH 80, BW, ASTM A420 GR.WPL6-WL03

<tbody>
</tbody>

Please suggest how resolve this problem with modification of same formula. I have lot of same cases in my excel file.

Regards,
Pradeep
 

Marcelo Branco

MrExcel MVP
The formula worked for me


A
B
C
D
E
1
Text​
Result​
MOC​
CODE​
2
90 DEG. LR ELBOW, SCH 80, BW, ASTM A420 GR.WPL6​
L02​
ASTM A420 GR.WPL6​
L02​
3
90 DEG. LR ELBOW, SCH 80, BW, ASTM A420 GR.WPL6-W​
L03​
ASTM A420 GR.WPL6-W​
L03​
4

Formula in B2 copied down
=LOOKUP(9.99999999999999E+307,SEARCH(MOC,$A2),CODE)

M.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top