How to Convert " ## to ##" to "##"?

Hello All Excel pros

This is excel noob here
I am trying to convert a string of values that are all "01 to 03" or "04 to 06" to a certain value.
For example, for "01 to 03" i want to give it a value, 3 on the cell beside.
How could I do that with formula or conditional formatting? Please advice.

Thanks!

1) Create a two-column table for reference.

For instance:
column M = string to match
column M = result you would want

2) Then use a VLOOKUP() formula to check your strings against the lookup table and bring back the wanted value.

Data Range

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Code

Results

String

Result

2

02 to 04

7

=VLOOKUP(A2,M:N, 2, 0)

01 to 03

3

3

01 to 03

3

02 to 04

7

4

03 to 05

11

5

05 to 09

13

6

7

2 stands for second column in M:N
0 stands for 'Exact Match'

it will get the exact match from second column which is column N

Please use Excel's builtin HELP files for things like this. Press F1 and enter the function you want the details for. They are quite good and at your fingertips.

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