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
Name	Number
Canisters	Canisters
Display Packs	display packs
Food Service	Food Service
Retail Packs	Retail Packs
PEPPER PACKETS 3000/.10 G
Pepper Packets 6000/.15g

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]144[/TD]
[TD="align: right"][/TD]

[TD="align: right"]144[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]48[/TD]
[TD="align: right"][/TD]

[TD="align: right"]48[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]24[/TD]
[TD="align: right"][/TD]

[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N2[/TH]
[TD="align: left"]=IFERROR(VLOOKUP(M2,$J$2:$K$7,2),LOOKUP(2,1/SEARCH($J$2:$J$7,R2),$K$2:$K$7))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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}),""))


[TABLE="class: grid, width: 474"]
<tbody>[TR]
[TD][/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Result[/TD]
[TD][/TD]
[TD]Value1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Value2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD="align: right"]144[/TD]
[TD][/TD]
[TD]Display Packs[/TD]
[TD][/TD]
[TD][/TD]
[TD]PEPPER PACKETS 3000/.10 G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]Canisters[/TD]
[TD][/TD]
[TD][/TD]
[TD]Pepper Packets 6000/.15g[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]Canisters[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]Another[/TD]
[TD][/TD]
[TD][/TD]
[TD]PEPPER PACKETS 3000/.10 G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD="align: right"]144[/TD]
[TD][/TD]
[TD]Display Packs[/TD]
[TD][/TD]
[TD][/TD]
[TD]Pepper Packets 6000/.15g[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**[/TD]
[TD]*******[/TD]
[TD]**[/TD]
[TD]************[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]**************************[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz