if text contains number between two values

[amirflax]

hi, i have a range with text - letters and numbers, i would like to set a text in the next cell according to the numbers in the range. for example:

A	B
CC189-001	ZONE2
CC190-003	ZONE2
CC172-004
CC194-005	ZONE2

i used the following formula in B1 and down:
=IF(SUM(COUNTIF(A1,{"*189*","*190*","*192*","*193*","*194*"})),"ZONE2","")
how can i change this formula to work between values? in this case 189-190 OR 192-194
thanks!

[AliGW]

Do you only have one zone? I am guessing this isn't quite the full picture.

There are instructions at the top of the page explaining how to attach your sample workbook.

[amirflax]

true, i have more than one zone
file attached
thank you

[AliGW]

Is this accurate? If so, how do we decide which zone when there is an overlap?

Excel 2016 (Windows) 32 bit

	E	F
1	ZONE1	ZONE2
2	159-199	189-194

Sheet: Sheet1

[amirflax]

oops, soory, zone1 is 195-199

[AliGW]

Right - let's start again. Correct your workbook, check it CAREFULLY and upload again.

[bebo021999]

IWA-G2-001
IWA-G2-002

What to do with this sort ?

[amirflax]

OK, i fixed the zones list

[bebo021999]

Try in B1:

Please Login or Register  to view this content.

Drag down

[huuthang_bd]

I have modified your lookup table to suit with the formula (yellow area in the image).

Please Login or Register  to view this content.

Capture.JPG

[amirflax]

thank you very much!!
this helped me alot

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Sorry for off-topic interjection:

@ huuthang_bd

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

[huuthang_bd]

Hi Ali,

Thank for your advice, I got it. The post was edited.

Huuthang