Adrian has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel Intermediate course
EXCEL ISNUMBER AND RANGES
HI
I am trying to categorise data within bank statements, i.e. searching if a the Description field contains text within a defined range and return the category in the 2nd column of that defined range. I am able to categorise without using the range, but not with it. Formula I am currently using is:
=IF(ISNUMBER(SEARCH(" PRET A MANGER ",C4)),"FOOD")
However, I want Pret A Manger to be within the range along with Food. I have defined this range, but it doesn't work. I want to use a range because I can always add to it, should more items be applicable.
Aany help would be much appreciated.
Example is below:
Column A
5660 06DEC16 C , PRET A MANGER , LONDON GB
'5660 06DEC16 , WWW.CLOTHES , 2ORDER.COM , MANCHESTER M1 GB
'5660 08DEC16 C , UPAY LTD , ROCHESTER GB
'5660 07DEC16 C , WHITE SWAN , LONDON WC2N GB
Column B
Has the formula
Range
Column C Column D
PRET A MANGER FOOD
2ORDER.COM CLOTHES
UPAY FOOD
WHITE SWAN DRINK
RE: EXCEL ISNUMBER AND RANGES
Hi Adrian,
Thank you for the forum question.
I am not sure I understand what you want but if you want to test if you have "PRET A MANGER" in a text string in a range, I would format the table as table. Click inside your range and on the home tab in the Styles group click Format as Table.
This will convert your table to a dynamic range, which automatically will update, when you add new records.
In the formula you just need to enter the heading from the the column you want to test in square brackets ([your heading]).
=IF(ISNUMBER(SEARCH(" PRET A MANGER ",[Description])),"FOOD")
I hope this can do what you want.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
