How to Extract Number in Cell with Letters

[Justinian]

I am using VLOOKUP (trying to at least) to find out how many rooms a hotel suite has. So in A1, for example, is suite 3302F. On another tab with all 1,000 suites numbers, I see 3302F in A23 and in B23, it says "Twr 1B." This is a one-bedroom suite because of the 1 but how do I extract just the number?

 

[Justinian]

Here, try using this:

https://app.box.com/s/kf4ib8ki90hjc8tpuxlfxgjjaf8itjay

 

[Scott Huish]

There's only 1 sheet in that file.

Not to mention you have items like this:
Twr2S#1
T1Sig#5

There are multiple sets of numbers in the data. Which numbers do you want extracted?

 

[Justinian]

Try this one. I need the formula to find the unit number entered on Sheet1 in A3 to find that unit on Sheet2 and extract the number of bedrooms.

https://app.box.com/s/kf4ib8ki90hjc8tpuxlfxgjjaf8itjay

 

[Scott Huish]

The only 4500G in your example has a value of 0 in Column B of Sheet1. 0 doesn't seem like it would be a correct answer.
Also, you haven't answered the question of multiple numbers, for example how many bedrooms does T1Sig#5 (There's a 1 and a 5 in this value) have and why?

 

[admiral100]

According to you sample file :

Enter this formula in sheet1 D3 =IFERROR(MID(A3,4,LEN(A3)-3),"") and drag down to D8473
And this one in sheet Dashboard B3 =(LOOKUP(9^9,0+MID(INDEX(Sheet1!B:B,MATCH(Dashboard!A3,Sheet1!D:D,0)),MIN(FIND({0,1,2,3,4,5,6,7,8,9},INDEX(Sheet1!B:B,MATCH(Dashboard!A3,Sheet1!D:D,0))&1/19)),ROW($1:$16))))

 

[Justinian]

According to you sample file :

Enter this formula in sheet1 D3 =IFERROR(MID(A3,4,LEN(A3)-3),"") and drag down to D8473
And this one in sheet Dashboard B3 =(LOOKUP(9^9,0+MID(INDEX(Sheet1!B:B,MATCH(Dashboard!A3,Sheet1!D:D,0)),MIN(FIND({0,1,2,3,4,5,6,7,8,9},INDEX(Sheet1!B:B,MATCH(Dashboard!A3,Sheet1!D:D,0))&1/19)),ROW($1:$16))))

This is what happens when I followed your notes:

https://app.box.com/s/kf4ib8ki90hjc8tpuxlfxgjjaf8itjay

 

[Justinian]

The only 4500G in your example has a value of 0 in Column B of Sheet1. 0 doesn't seem like it would be a correct answer.
Also, you haven't answered the question of multiple numbers, for example how many bedrooms does T1Sig#5 (There's a 1 and a 5 in this value) have and why?

The suites with 0 next to them are studios (0 bedrooms) so all I need there is the formula to deliver the 0.

Sorry, for T1Sig#5, the number of bedrooms is the 1.

 

[admiral100]

This is because your Excel does not automatically calculate ...
On the "Formulas" tab> "Calculation Options"> select "Auto"

 

[admiral100]

Click on B3 then paste the formula into Formula bar and press enter Or double click on B3 paste the formula and press enter .

If it's asking Yes/No click on Yes.

 

[Justinian]

This is because your Excel does not automatically calculate ...
On the "Formulas" tab> "Calculation Options"> select "Auto"

It is set to auto. That was the first thing I checked.