# Thread: How to reference a number without a hyphen (2003)

1. ## How to reference a number without a hyphen (2003)

In sheet 1, my data is listed like this 01-96-5, 85-17-1, 09-26-5. In sheet 2, the table that I reference to data looks like 01965, 85171, 09265 and the return value that I want is located in the next column. Could you assist? Thank you.

2. ## Re: How to reference a number without a hyphen (2003)

If I am reading your question right you have 01-96-5, 85-17-1, 09-26-5 in a column (let us assume column A in sheet 1 starting at A1) you then have 01965, 85171, 09265 in a column (let us assume in column A in sheet 2 starting at A1.

For this formula to work the dat in sheet 2 column A should be in A-Z order and in Sheet1!B1 use this formula and copy down:

=VLOOKUP(LEFT(A3,2)&MID(A3,4,2)&RIGHT(A3,1),Sheet2 !\$A\$1:\$B\$3,2)

Otherwise match/index may assist

3. ## Re: How to reference a number without a hyphen (2003)

Yoo..hoo...it works. Kudos to you Jerry!

4. ## Re: How to reference a number without a hyphen (20

I think Jezza's formula missing a zero at the end of the formula

=VLOOKUP(LEFT(A3,2)&MID(A3,4,2)&RIGHT(A3,1),Sheet2 !\$A\$1:\$B\$3,2,0)

And, or try this alternative one……

=VLOOKUP(SUBSTITUTE(A1,"-",""),Sheet2!A\$1:B\$3,2,0)

Regards
Bosco

5. ## Re: How to reference a number without a hyphen (20

It appears that there is a little issue. Your formula works in my practice sheet but not my workbook because of the format. when I place the cursor in cell A3 of sheet 1, it shows me 1965. When I place the cursor on cell A3 of sheet 2, I get '01965. I did try to format both at text and number but I get the error message. Please advice. Thank you.

6. ## Re: How to reference a number without a hyphen (20

I thought that Column A of Sheet 1 looked like 01-96-5.
I suggest that you post the workbook stripped down to only a small portion of Sheets 1 & 2. Then tell us what you expect to see as a result in a specific cell.

7. ## Re: How to reference a number without a hyphen (20

Thank you for your reply Don. I was able to correct my formula =VLOOKUP(TEXT(A1,"00000"),Sheet2!A\$1:B\$30,2,) since the hyphens and zero are not REAL. It appears that I can't differentiate between a value or a text. It's a USER ERROR issue. If the format is Custom, would that consider a value or text? Thank you.

8. ## Re: How to reference a number without a hyphen (20

The lookups work with the values in the cells not what is displayed. [As opposed to something like Datafiltering which uses the display and not the value).

If you want to lookup on the display text, you can create an intermediate column which uses the TEXT function to convert the value into formatted text and then lookup in this column.

Steve

9. ## Re: How to reference a number without a hyphen (20

That was very informative. Thank you Steve!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•