If statement that will extract a number from text

kmpisano · Jul 24, 2013

Hi- I am working on a spreadsheet storing information about purchases. If someone buys a single item, the cell states "single item ###" (the ### being the code of the item). If not, there is a random string of letters and numbers. I have made an if statement stating that If the cell has the words "single item" it will give me the content of the cell to the left. If not, it will give me 0.

Instead of the if statement returning the content of the cell to the left, I want the formula to look up the actual single product number (stated above as "###") and give me that.

Here is the lookup formula I used: =IF(ISNUMBER(SEARCH("single issue",E7)),E7,0)

I'm not sure if i have to do a v look or extra a number, etc.

I appreciate any help you can give!

shift-del · Jul 24, 2013

Hello

Try:

Code:

[COLOR=#222222]=IF[/COLOR][COLOR=#0000dd](ISNUMBER[/COLOR][COLOR=#222222](SEARCH[/COLOR][COLOR=#0000dd]("single  item",A1)[/COLOR][COLOR=#222222])[/COLOR][COLOR=#0000dd],MID[/COLOR][COLOR=#222222](A1,13,9^9)[/COLOR][COLOR=#0000dd],0)[/COLOR]

kmpisano · Jul 24, 2013

I need a VLOOKUP in sheet 1 that will look into the tab ”Apple Data” for the exact text in row A of Sheet 1. So for example, Lets take Sheet1 cell A31 which has the text: “Single Issue 583”. I want the formula to look for “Single Issue 583” in row E of “Apple Data tab”. If it finds it, I want it to take the value from row N of “Apple Data” and give me the sum of those numbers.

So “Single Issue 583” can be found in cells E7 and E8 of “Apple Data”. N7 has a value of 1 and N8 has a value of 1. So, the v lookup, which will be located in cell B31 of “Sheet1”, should give me a value of 2.

Do you know how to do this?

Jonmo1 · Jul 24, 2013

That's SUMIF

=SUMIF('Apple Data'!E:E,A31,'Apple Data'!N:N)

FYI..
E and N are Columns
7 and 8 are Rows

If statement that will extract a number from text

kmpisano

New Member

Excel Facts

shift-del

Well-known Member

kmpisano

New Member

Jonmo1

MrExcel MVP

Similar threads

Forum statistics

Share this page

If statement that will extract a number from text

kmpisano

New Member

Excel Facts

shift-del

Well-known Member

kmpisano

New Member

Jonmo1

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock