return a value in one cell based on multiple formulas
 

I have the following data:
gift_id suffix gift_desc size
BX02 Chateauneuf - Semi Dry White 750 ml (12)
BX07 Cabernet Sauvignon (VDP) 750 ML (12)
BX08 Valrose (VDP) 750 ML (12)
CH02 NYS White Sparkling 187 ML (24)
CH02 NYS White Sparkling 750 ml (12)

I would like to return a value in the "suffix" column based on the text in
the "size" field.
for example =IF(ISNUMBER(SEARCH("750",D2)),-750). I want -750 to appear in
the suffix column if partial text "750" appears in the size column and
similarly I want 187 to appear in the suffix column if partial text "187"
appears in the size column. Similarly, there are other values I want to
appear based on values later in the spreadsheet. My real goal is to append
the gift_id with those values in the suffix column. Does any one have any
ideas?

return a value in one cell based on multiple formulas
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"eliyahuz" wrote in message
...
I have the following data:
gift_id suffix gift_desc size
BX02 Chateauneuf - Semi Dry White 750 ml (12)
BX07 Cabernet Sauvignon (VDP) 750 ML (12)
BX08 Valrose (VDP) 750 ML (12)
CH02 NYS White Sparkling 187 ML (24)
CH02 NYS White Sparkling 750 ml (12)

I would like to return a value in the "suffix" column based on the text in
the "size" field.
for example =IF(ISNUMBER(SEARCH("750",D2)),-750). I want -750 to appear in
the suffix column if partial text "750" appears in the size column and
similarly I want 187 to appear in the suffix column if partial text "187"
appears in the size column. Similarly, there are other values I want to
appear based on values later in the spreadsheet. My real goal is to append
the gift_id with those values in the suffix column. Does any one have any
ideas?

return a value in one cell based on multiple formulas
 

If you are just wanting to extract the number (which will be the first part
of the value) could something like this work?

=LEFT(D2,FIND(" ",D2)-1)

One step further:
=A2&"-"&LEFT(D2,FIND(" ",D2)-1)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"eliyahuz" wrote:

I have the following data:
gift_id suffix gift_desc size
BX02 Chateauneuf - Semi Dry White 750 ml (12)
BX07 Cabernet Sauvignon (VDP) 750 ML (12)
BX08 Valrose (VDP) 750 ML (12)
CH02 NYS White Sparkling 187 ML (24)
CH02 NYS White Sparkling 750 ml (12)

I would like to return a value in the "suffix" column based on the text in
the "size" field.
for example =IF(ISNUMBER(SEARCH("750",D2)),-750). I want -750 to appear in
the suffix column if partial text "750" appears in the size column and
similarly I want 187 to appear in the suffix column if partial text "187"
appears in the size column. Similarly, there are other values I want to
appear based on values later in the spreadsheet. My real goal is to append
the gift_id with those values in the suffix column. Does any one have any
ideas?