Search text and display required information

[PRADEEPSWALSE]

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.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 437"]
<tbody>[TR]
[TD="class: xl65, width: 437"]PIPE SIZE, 20NB, SCH STD, ASTM A106 GR.B, PE, SEAMLESS, B 36.10M[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 437"]
<tbody>[TR]
[TD="class: xl65, width: 437"]PIPE SIZE, 15NB, SCH STD, ASTM A312 TP304, PE, SEAMLESS, B 36.10M[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 437"]
<tbody>[TR]
[TD="class: xl65, width: 437"]PIPE SIZE, 15NB, SCH XS, ASTM A333 GR.6, PE, SEAMLESS, B 36.10M[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 437"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 437"]
<tbody>[TR]
[TD="class: xl65, width: 437"]PIPE SIZE, 15NB, SCH XXS, ASTM A106 GR.B, PE, SEAMLESS, B 36.10M[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]














[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 106"]
<tbody>[TR]
[TD="class: xl65, width: 106"]MOC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]CODE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 106"]
<tbody>[TR]
[TD="class: xl65, width: 106"]ASTM A106 GR.B[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]C01[/TD]
[/TR]
[TR]
[TD][TABLE="width: 106"]
<tbody>[TR]
[TD="class: xl65, width: 106"]ASTM A333 GR.6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]L01[/TD]
[/TR]
[TR]
[TD][TABLE="width: 106"]
<tbody>[TR]
[TD="class: xl65, width: 106"]ASTM A312 TP304[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]S01[/TD]
[/TR]
</tbody>[/TABLE]











Please help.

 

[oldbrewer]

where does CO1 come from ?

 

[PRADEEPSWALSE]

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.

 

[Marcelo Branco]

Try

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

M.

 

[PRADEEPSWALSE]

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]

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]

Hello,

Okay.

Now I am facing another problem.

MOC	CODE
ASTM A420 GR.WPL6	L02
ASTM A420 GR.WPL6-W	L03

<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.


[TABLE="width: 449"]
<tbody>[TR]
[TD]90 DEG. LR ELBOW, SCH 80, BW, ASTM A420 GR.WPL6[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]90 DEG. LR ELBOW, SCH 80, BW, ASTM A420 GR.WPL6-W[/TD]
[TD]L03[/TD]
[/TR]
</tbody>[/TABLE]

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]

The formula worked for me


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Text​

[/td][td]

Result​

[/td][td][/td][td]

MOC​

[/td][td]

CODE​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

90 DEG. LR ELBOW, SCH 80, BW, ASTM A420 GR.WPL6​

[/td][td]

L02​

[/td][td][/td][td]

ASTM A420 GR.WPL6​

[/td][td]

L02​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

90 DEG. LR ELBOW, SCH 80, BW, ASTM A420 GR.WPL6-W​

[/td][td]

L03​

[/td][td][/td][td]

ASTM A420 GR.WPL6-W​

[/td][td]

L03​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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

M.

 

[Marcelo Branco]

A more robust formula

=LOOKUP(9.99999999999999E+307,SEARCH(" "&MOC&" "," "&SUBSTITUTE($A2,","," ")&" "),CODE)

M.