if statement to find a number and return that number in a cell

**Neelam82** · 05-07-2013, 09:37 PM

Hi,

I have this scenario below, in column A I have the descriptions and in column B I would like it to look up column A and find "2P" or "3P" and return just the numerical value in column B. I have tried with "if" and "right" statements but I cannot seem to get it right. The purpose of this exercise is just to find out how many packs are in each of the styles (ignoring the 90ml/3oz sizes, if possible). Thanks for your assistance.

A B
1 Everyday Pants 2P
2 Everyday Pants 3P
3 Shape Sleek Short
4 Silhouette Pants
5 Soak Bottle 90mL/3oz
6 Everyday Pants 4P

Regards,
Neelam

**vlady** · 05-07-2013, 10:36 PM

Formula:

Please Login or Register  to view this content.

**Neelam82** · 05-07-2013, 11:22 PM

Thanks vlady. But this forumla did not work

**FDibbins** · 05-07-2013, 11:38 PM

Hi and welcome to the forum

a cpl of questions for you...
1. what is not working? please explain what results you are getting and what results you were expecting.
2. what data is in column A and what is in column B - your sample data is not that clear. i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Neelam82** · 05-08-2013, 12:56 AM

Hi,

The result was blank. I have attached the example file.

Thank you.

**FDibbins** · 05-08-2013, 01:03 AM

thanks.

Try this, copied down..
=IFERROR(MID(A2,SEARCH("2P",A2,1),1),IFERROR(MID(A2,SEARCH("3P",A2,1),1),""))
if you want the answer to be a value instead of text, use this...
=IFERROR(MID(A2,SEARCH("2P",A2,1),1)*1,IFERROR(MID(A2,SEARCH("3P",A2,1),1)*1,""))

**Neelam82** · 05-08-2013, 02:03 AM

Thank you. That worked perfect.

**vlady** · 05-08-2013, 08:48 PM

sorry bout that my bad, the typo on my part in "P@" just to correct my given formula.

=IFERROR(LEFT(TRIM(RIGHT(SUBSTITUTE(A2&"@"," ",REPT(" ",LEN(A2&"@")-1)),LEN(A2&"@"))),FIND("P@",TRIM(RIGHT(SUBSTITUTE(A2&"@"," ",REPT(" ",LEN(A2&"@")-1)),LEN(A2&"@"))))-1)*1,"")

this will get the value regardless of what is the numeric value before the P character. say 500P , 23P, etc.

Thanks.

**Teethless mama** · 05-08-2013, 10:22 PM

Try my formula. It will work either the "numeric +P" in front, in the middle, or at the end. See the attached

if statement to find a number and return that number in a cell

LinkBack

Thread Tools

Rate This Thread

Display

if statement to find a number and return that number in a cell

Re: if statement to find a number and return that number in a cell

Re: if statement to find a number and return that number in a cell

Re: if statement to find a number and return that number in a cell

Re: if statement to find a number and return that number in a cell

Re: if statement to find a number and return that number in a cell

Re: if statement to find a number and return that number in a cell

Re: if statement to find a number and return that number in a cell

Re: if statement to find a number and return that number in a cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions