Extract String From A Cell and Display

[sureshsonti]

Hello,
I have two columns in a table Materials Plant and Materials. I am trying to extract only a string from Materials Plant column to Materials column

Example:
I have PEAGRAVEL in Materials Plant column but the formula that I got only extracts GRAVEL to the Materials column

I have attached the spreadsheet.

Thanks in advance

 

[bosco_yip]

Try,

K8, copied down :

=IF(ISNUMBER(SEARCH(" GRAVEL "," "&[@[MATERIAL EXTRACT]]&" ")),"GRAVEL",IF(ISNUMBER(SEARCH(" SAND "," "&[@[MATERIAL EXTRACT]]&" ")),"SAND",IF(ISNUMBER(SEARCH(" PEAGRAVEL "," "&[@[MATERIAL EXTRACT]]&" ")),"PEAGRAVEL","NO")))

Regards
Bosco

 

[Haz]

How about
=IFERROR(LEFT([@[MATERIAL EXTRACT]], SEARCH(" ", [@[MATERIAL EXTRACT]])-1), "NO")

 

[bosco_yip]

Or...............

=LEFT([@[MATERIAL EXTRACT]], SEARCH(" ", [@[MATERIAL EXTRACT]]&" ")-1)

Regards
Bosco

 

[Peter Bartholomew]

= LOOKUP( 1, 1 / (SEARCH(material,[@[MATERIAL EXTRACT]]) = 1), material)

This starts with naming a list of the (three) material options. The material extract field is searched for each material as an array and tested against 1 (leftmost character). LOOKUP returns the valid material from the list.

 

[sureshsonti]

= LOOKUP( 1, 1 / (SEARCH(material,[@[MATERIAL EXTRACT]]) = 1), material)

This starts with naming a list of the (three) material options. The material extract field is searched for each material as an array and tested against 1 (leftmost character). LOOKUP returns the valid material from the list.

Or...............

=LEFT([@[MATERIAL EXTRACT]], SEARCH(" ", [@[MATERIAL EXTRACT]]&" ")-1)

Regards
Bosco

Thanks Mr. Bosco. All your solutions worked. Thanks again

 

[sureshsonti]

How about
=IFERROR(LEFT([@[MATERIAL EXTRACT]], SEARCH(" ", [@[MATERIAL EXTRACT]])-1), "NO")

Thanks Haz your solution also worked. Thanks again

 

[sureshsonti]

Or...............

=LEFT([@[MATERIAL EXTRACT]], SEARCH(" ", [@[MATERIAL EXTRACT]]&" ")-1)

Regards
Bosco

Mr. Bosco
Need your help again.
I am trying extract just the vendor name from MATERIAL PLANT VENDOR field to VENDOR field

example:
SAND PL 01 -BALDWIN REDI MIX from this I only need text after the dash or hyphen BALDWIN REDI MIX. It should extract any data after the dash or hyphen

I have attached the spreadsheet.

Thanks again

 

[bosco_yip]

Mr. Bosco
Need your help again.
I am trying extract just the vendor name from MATERIAL PLANT VENDOR field to VENDOR field

example:
SAND PL 01 -BALDWIN REDI MIX from this I only need text after the dash or hyphen BALDWIN REDI MIX. It should extract any data after the dash or hyphen

I have attached the spreadsheet.

Thanks again

Try,

In I6, copied down :

=MID([@[MATERIAL PLANT VENDOR]],SEARCH("-",[@[MATERIAL PLANT VENDOR]]&"-")+1,99)

or,

=SUBSTITUTE([@[MATERIAL PLANT VENDOR]],[@[MATERIALS PLANTS]]&" -","")

Regards
Bosco

 

[sureshsonti]

Thanks Mr. Bosco. Will post it in the original post going forward. Thanks again.