pulling text from a string

[MikeyZ]

How can I pull certain text from a string using an fx?

In my target column I only have two different sentences.

OSV to Bulk Anodize
OSV to Rack Anodize

depending on which one it is, I only want to extract either the Bulk or the Rack text.

How can I do that?

 

[nardagus]

Since you have only two sentences, both have the same amount of chars, and both words also have the same amount of chars...
Let's say your target column is E. You could write this:

Excel 2016 (Windows) 32 bit

	E	F
OSV to Bulk Anodize	Bulk
OSV to Rack Anodize	Rack

<tbody>
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]F2[/TH]
[TD="align: left"]=MID(E2,8,4)[/TD]
[/TR]
[TR]
[TH]F3[/TH]
[TD="align: left"]=MID(E3,8,4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[MikeyZ]

Not always the case.
Some might say: Bulk Anodize Red, Rack Anodize Only OSV to Rack Anodize

Should have been more specific, sorry.

 

[Joe4]

Here is one way (for an example in cell A1):

=IF(ISNUMBER(FIND("Bulk",A1)),"Bulk",IF(ISNUMBER(FIND("Rack",A1)),"Rack",""))

 

[Rick Rothstein]

Here is one way (for an example in cell A1):

=IF(ISNUMBER(FIND("Bulk",A1)),"Bulk",IF(ISNUMBER(FIND("Rack",A1)),"Rack",""))

The OP's example in Message #3 has both key words in it, so I think you will need to change this to look for the key phrases the OP said would be in the text...

=IF(ISNUMBER(FIND("OSV to Bulk Anodize",A1)),"Bulk",IF(ISNUMBER(FIND("OSV to Rack Anodize",A1)),"Rack",""))

 

[MikeyZ]

I tried the fx with H2 replacing A1. Didn't work.
The text was as follows: Rack. The Fx has it in all CAPS. So I tried replacing the RACK with Rack and it worked.
How do I get around the case sensitivity?

Typing this has been a challenge because for some reason not al the letters I hit on my keyboard register on the screen. I have a new keyboard.

 

[Rick Rothstein]

I tried the fx with H2 replacing A1. Didn't work.
The text was as follows: Rack. The Fx has it in all CAPS. So I tried replacing the RACK with Rack and it worked.
How do I get around the case sensitivity?

Maybe this...

=IF(ISNUMBER(SEARCH("OSV to Bulk Anodize",H2)),"Bulk",IF(ISNUMBER(SEARCH("OSV to Rack Anodize",H2)),"Rack",""))

 

[jtakw]

How can I pull certain text from a string using an fx?

In my target column I only have two different sentences.

OSV to Bulk Anodize
OSV to Rack Anodize

depending on which one it is, I only want to extract either the Bulk or the Rack text.

How can I do that?

Hi,

Just another way.

Your description seems to imply you have the criterion/sentences you want to look up in a column.
You can "hard-code" the results in the formula (as in my sample B1), or you can Also list the results you want in a column and use cell references (as in my sample C1).
With the latter, you can easily "add" sentences and results to the "list" if needed, and change the LOOKUP range and RESULT range in the formula.

Book1
	A	B	C	F	G	H
1	Bulk Anodize Red, Rack Anodize Only OSV to Rack Anodize	Rack	Rack		OSV to Bulk Anodize	Bulk
2	Bulk Anodize Red, OSV to Bulk Anodize, Rack Anodize Only	Bulk	Bulk		OSV to Rack Anodize	Rack
3	Bulk Anodize Red, Bulk Anodize Only, Rack Anodize Only
Sheet159

Cell Formulas
Range	Formula
B1	=IFERROR(LOOKUP(2,1/SEARCH(G$1:G$2,A1),{"Bulk","Rack"}),"")
C1	=IFERROR(LOOKUP(2,1/SEARCH(G$1:G$2,A1),H$1:H$2),"")

Formulas copied down.