Return Values with in Cell

N

nycard64

Board Regular
Joined
Dec 23, 2009
Messages
113
I have a formula to look up values within a cell

=IF( SUM(--ISNUMBER(SEARCH({"4S","3S"}, A2))),"4S","N")

It returns 4S if the cell contains 4S or 3S, is there are way I can have the formula return 4S if contains 4S, and 3s if contains 3S?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Here is kind of a "round-a-bout" solution, but it works.

=IF( SUM(--ISNUMBER(SEARCH({"4S"}, A2))),"4S",IF(SUM(--ISNUMBER(SEARCH({"3S"}, A2))),"3S"))
 
Upvote 0
I'm not sure what you are using the ISNUMBER and SEARCH formulas for, but you could simplify the formula even further:

=IF(A2="4S","4S",IF(A2="3S","3S",""))
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
IF Formulas "containing" text
Replies
1
Views
508
awoohaw
awoohaw
JenniferMurphy
  • Solved
How to generate weighted random numbers?
3 4 5
Replies
41
Views
6K
johnnyL
J
G
  • Solved
Adding cells with numbers and a letter designation to specific cells
Replies
4
Views
663
kweaver
kweaver
JenniferMurphy
  • Solved
How to compute standard deviation from tallies
Replies
4
Views
389
JenniferMurphy
JenniferMurphy
G
  • Solved
Return location of specific text in workbook
2
Replies
10
Views
222
mumps
mumps

Forum statistics

Threads
1,224,597
Messages
6,179,813
Members
452,945
Latest member
Bib195

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top