Cell formula if it contains a certain number within a string of text

[djnett]

Is there a formula that can look at the contents of a cell and return a value based on that number. I think it's more complex than a basic If statement but not really sure.

In my example I need to know first if the number 3000 or 6000 appear in the cell. If 3000 appears than a 3 is returned in the cell, if 6000 appears than 6 will appear.

PEPPER PACKETS 3000/.10 G ....would return the number 3

Pepper Packets 6000/.15g...would return the value 6

Appreciate any help anyone can provide!

Regards,

-Dave

 

[dave2018]

THis should get you started...

=IF(ISERROR(FIND("3000",A1))=FALSE,3,IF(ISERROR(FIND("6000",A1))=FALSE,6,""))

 

[Scott T]

You did not say what should happen if one of the numbers is not found this will return blank. If your number has the thousand marker like PEPPER PACKETS 3,000/.10 G then you would need to include that in the formula

=IF(ISNUMBER(SEARCH("3,000",A1)),3,IF(ISNUMBER(SEARCH("6,000",A1)),6,""))

Excel 2010
	A	B
1	PEPPER PACKETS 3000/.10 G	3
2	Pepper Packets 6000/.15g	6
3	this does not have number
Sheet4

Cell Formulas
Range	Formula
B1	=IF(ISNUMBER(SEARCH("3000",A1)),3,IF(ISNUMBER(SEARCH("6000",A1)),6,""))
B2	=IF(ISNUMBER(SEARCH("3000",A2)),3,IF(ISNUMBER(SEARCH("6000",A2)),6,""))
B3	=IF(ISNUMBER(SEARCH("3000",A3)),3,IF(ISNUMBER(SEARCH("6000",A3)),6,""))

 

[djnett]

This formula works perfectly!!!! thank you!

To make this work even better in my spreadsheet is it possible to combine to different formulas in the same column? I have several products that will return different values depending on the two cells I'm working with...

1) =IFS(M2="Canisters","12", M2="Display Packs","144", M2="Food Service", "48",M2="Retail Packs","24")

2) =IF(ISNUMBER(SEARCH("3000,R2)),3,if(ISNUMBER(SEARCH("6000",R2)),6,""))

So the first formula looks at column M and returns a value depending on the four different products in the column. For the fifth product I have to look in a different column...R...and based on the quantity value of 3000 or 6000 it will return the value 3 or 6 based on the formula you guys created.

Is it possible to combine the two formulas and if a value is derived from formula 1 I use that value but if it didn't create a value formula 2 will return a value. Does that make sense?

 

[Scott T]

If I understand what you want to do correctly you can join the formulas together with a space in between.

=IFS(M2="Canisters","12", M2="Display Packs","144", M2="Food Service", "48",M2="Retail Packs","24")& " "&IF(ISNUMBER(SEARCH("3000,R2)),3,if(ISNUMBER(SEARCH("6000",R2)),6,""))

 

[Eric W]

When you have multiple conditions like this, it's usually better to build a table. It keeps the formula shorter, and makes it much easier to change in the future. For example:

	J	K	L	M	N	O	P	Q	R
1	Name	Number
2	Canisters	12		Canisters	12
3	Display Packs	144		display packs	144
4	Food Service	48		Food Service	48
5	Retail Packs	24		Retail Packs	24
6	6000	6			3				PEPPER PACKETS 3000/.10 G
7	3000	3			6				Pepper Packets 6000/.15g

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula N2 =IFERROR(VLOOKUP(M2,$J$2:$K$7,2),LOOKUP(2,1/SEARCH($J$2:$J$7,R2),$K$2:$K$7)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I used the same table for both conditions, but you may want to build 2 tables in this case.

 

[markmzz]

This formula works perfectly!!!! thank you!
To make this work even better in my spreadsheet is it possible to combine to different formulas in the same column? I have several products that will return different values depending on the wo cells I'm working with...
1) =IFS(M2="Canisters","12", M2="Display Packs","144", M2="Food Service", "48",M2="Retail Packs","24")
2) =IF(ISNUMBER(SEARCH("3000,R2)),3,if(ISNUMBER(SEARCH("6000",R2)),6,""))
So the first formula looks at column M and returns a value depending on the four different products in the column. For the fifth product I have to look in a different column...R...and based on the quantity value of 3000 or 6000 it will return the value 3 or 6 based on the formula you guys created.
Is it possible to combine the two formulas and if a value is derived from formula 1 I use that value but if it didn't create a value formula 2 will return a value. Does that make sense?

Hi!

Try the formula below (that do the job too):

=IFERROR(VLOOKUP(M2,{"Canisters",1;"Retail Packs",2;"Food Service",4;"Display Packs",12},2,0)*12,
IFERROR(LOOKUP(8^9,FIND({3;6}*1000,R2),{3;6}),""))

	J	K	L	M	N	Q	R	S
1		Result		Value1			Value2
2		144		Display Packs			PEPPER PACKETS 3000/.10 G
3		12		Canisters			Pepper Packets 6000/.15g
4		12		Canisters
5		3		Another			PEPPER PACKETS 3000/.10 G
6		144		Display Packs			Pepper Packets 6000/.15g
7
***	**	*******	**	************	**	**	**************************	**

<tbody>
</tbody>

Markmzz