Calc a formula conundrum

users
#1

Hello
A Calc conundrum
I’m wondering if there is a simpler way of doing this

Cell I5 contains a line of text from a bank statement the content will
include or not a key word,
key words are Lidl, SE Gas, Water etc etc
The formula below (in C5) looks at content in cell I5 if the cell contains
one of the key words then a corresponding name is produced in cell C5 ie.
Lidl = Lidl, Water = Water etc etc.

The formula does work, however it is long and is laborious to add new key
words and I’m hoping someone might know a better way of doing it.
New key words would be ‘HOLLAND & BARRETT’ and ‘PAYPAL’ and more as needed.
I’m wondering if its possible to have a list of key words ?

I’ve attached an example – which will make my explanation more clear ? I
hope

=IF(ISNUMBER(FIND("LIDL",I5)),"LIDL",IF(ISNUMBER(FIND("SE
GAS",I5)),"GAS",IF(ISNUMBER(FIND("WATER",I5)),"WATER
",IF(ISNUMBER(FIND("LEGAL & GENERAL",I5)),"LEGAL & GENERAL
",IF(ISNUMBER(FIND("SOUTHERN",I5)),"ELECTRIC",IF(ISNUMBER(FIND("TALKTALK",I5)),"TalkTalk",IF(ISNUMBER(FIND("MORRISON",I5)),"MORRISON",IF(ISNUMBER(FIND("ALDI",I5)),"ALDI",IF(ISNUMBER(FIND("HERON",I5)),"HERO
BANK_STATEMENT.ods
<http://nabble.documentfoundation.org/file/n4199203/BANK_STATEMENT.ods>
N","NO"))))))))

#2

BANK_STATEMENT.ods
<http://nabble.documentfoundation.org/file/n4199204/BANK_STATEMENT.ods>

the attachment didn't seem to upload so trying again

#3

IGraham wrote

I’m wondering if there is a simpler way of doing this

Use the function FIND to locate the word TO and then get all text after it
(100 characters should be enough...)

=MID(I5;FIND("TO";I5)+3;100)

#4

  The formula does work, however it is long and is laborious to add new key
words and I’m hoping someone might know a better way of doing it.
New key words would be ‘HOLLAND & BARRETT’ and ‘PAYPAL’ and more as needed.
I’m wondering if its possible to have a list of key words ?

This may help with your key-word-list.

    MATCH

Returns the relative position of an item in an array that matches a specified value. The function returns the position of the value found in the lookup_array as a number.

      Syntax

MATCH(SearchCriterion; LookupArray; Type)|

Find more information at:

https://help.libreoffice.org/Calc/Spreadsheet_Functions#VLOOKUP

#5

For a tutorial video on 'index' and 'match' see "LibreOffice Calc - Index and Match": https://www.youtube.com/watch?v=8zydk03ILXs

#6

Thank, a couple of things to experiment with, but thats going to have to
wait till tomorrow, i shall report results eventually

#7

Hello,

The easiest would be to use VLOOKUP() with maybe some massaging of the
statement messages. I have updated your file with two examples (columns E
and F of Sheet1). In my opinion, this would be the easiest way to support
multiple additions with a minimum of headaches.

BANK_STATEMENT.ods
<http://nabble.documentfoundation.org/file/n4199275/BANK_STATEMENT.ods>

I hope this helps.

Rémy Gauthier.

#8

First, apology for the late reply
Second a thought directed at me by me 'be careful what you wish for'
Third, thanks Rémy

Rémy your understanding of spreadsheets is obviously a great deal deeper
than mine, your solution does what i want.
:slight_smile: i just have to work out what its doing now, I've never seen the
'SUBSTITUTE' function before, going to be fun working out whats going on, i
can feel a headache coming on already

again thanks