EXCEL ISNUMBER AND RANGES

Resolved · Medium Priority · Version 2010

Sun 8 Jan 2017, 10:29 Delegate Adrian said...

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

Mon 9 Jan 2017, 09:31 Trainer Jens said...

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Display Functions on Worksheets

Functions in Excel can be difficult to recall their format/syntax

For example, you want to use the =PMT function.

Enter =PMT, then select keystroke, CTRL+SHIFT+A.

This usful memory jog, will display the arguments of a function on a worksheet, allowing the user to proceed with the generation of the function

View all Excel hints and tips