Apache OpenOffice Community Forum

Posted: **Mon Jul 02, 2012 9:02 am**

Hi there,
I tried Googling this (for hours) to find a pre-existing answer - however I'm afraid I still can't work this out by myself (I'm a bit of newbie to OpenOffice Calc).

I have a large sheet of product data where all product attributes are stored in a single column - A. The column data looks like the list below

SOUP PLATE-300mm W/LID AURA (931/0232)
ROUND PLATE-FLAT 240mm AURA (931/1074)
ROUND PLATE-280mm CHELSEA RIM SHAPE (0941)
SQUARE PLATE 240x240mm MAXIM
ROUND PLATE-WIDE RIM 160mm MAXIM
SQUARE PLATE DEEP-140mm CHELSEA (4110)

There are 1,000s of rows and I am trying to extract the Brand name data (that's "Aura", "Chelsea", "Maxim") and write this data into the adjacent Column B.

I tried to write a formula that cycles through a list of known Brand Names - and if it found "TRUE" to a brand name, it would write this to the adjacent column B. While I can work out how to do an IF statement that works for one brand, I can't configure it to test for multiple IFs in the same statement.

=IF(ISNUMBER(SEARCH("Chelsea";A2));"Chelsea";"")
=IF(ISNUMBER(SEARCH("Maxim"; A10));"Maxim";"")
=IF(ISNUMBER(SEARCH("AURA";A7));"Aura";"")

Any tips on how to do this would be greatly appreciated!!

Posted: **Mon Jul 02, 2012 9:52 am**

You can replace the "false" part of each IF (the "") with another IF, so it will be:

=IF(ISNUMBER(SEARCH("Chelsea";A2));"Chelsea";IF(ISNUMBER(SEARCH("Maxim"; A10));"Maxim";IF(ISNUMBER(SEARCH("AURA";A7));"Aura";"")))

There is a limit in the number of nested IF's, around 20 or so.

Another method is to use the "&" sign to concatenate the results of the IF's:

=IF(ISNUMBER(SEARCH("Chelsea";A2));"Chelsea";"") & IF(ISNUMBER(SEARCH("Maxim"; A10));"Maxim";"") & IF(ISNUMBER(SEARCH("AURA";A7));"Aura";"")

In this case, I think the only limit is the length of a formula, something like 32,000 characters.

Posted: **Mon Jul 02, 2012 10:08 am**

Thanks squenson, you saved me hours upon hours of time! Cheers

Posted: **Mon Jul 02, 2012 11:32 am**

Hallo

Little bit sophisticated but without many IFs:

Code: Select all

=INDEX(F$1:F$4;MATCH(1;COUNTIF(A2;".*"&E$1:E$4&".*");0))

as [x]Matrix in Formulawizard, and with
→Tools→Options→Calc→Calculate→[x]allow regular Expressions in Formula

E1:F4 has content:

Code: Select all

Chelsea  Chelsea
Maxim    Maxim
Aura     Aura
<empty>  other

Karo

Posted: **Mon Jun 22, 2015 10:31 pm**

squenson wrote:You can replace the "false" part of each IF (the "") with another IF

Thank you, this technique was very useful for me.

Apache OpenOffice Community Forum

[Solved] Multiple IF ELSE statements?

[Solved] Multiple IF ELSE statements?

Re: Multiple IF ELSE statements?

Re: Multiple IF ELSE statements?

Re: [Solved] Multiple IF ELSE statements?

Re: Multiple IF ELSE statements?