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?

 

[admiral100]

Hi,

Try this: =(LOOKUP(9^9,0+MID(B23,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B23&1/19)),ROW($1:$16))))

 

[Scott Huish]

Assuming that you could never go over 9B and it always is in the last 2 characters:
=LEFT(RIGHT(B23,2))+0

 

[rconverse]

I've used this and it's worked for me. You'll need to add the code for this function.

Function GetNumbers(S As String) As String
Dim i As Long
For i = 1 To Len(S)
    If Mid(S, i, 1) Like "[1-9]" Then GetNumbers = GetNumbers & Mid(S, i, 1)
Next i
End Function

Your formula in the cell could then reference that function.

=GetNumbers(A2)

 

[Justinian]

Assuming that you could never go over 9B and it always is in the last 2 characters:
=LEFT(RIGHT(B23,2))+0

Sorry, I should have stated not all numbers are in same place. For example, I could also have 2 GU or 3Sig.

 

[Justinian]

Hi,

Try this: =(LOOKUP(9^9,0+MID(B23,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B23&1/19)),ROW($1:$16))))

Where is this formula referencing the second tab?

 

[Justinian]

I've used this and it's worked for me. You'll need to add the code for this function.

Function GetNumbers(S As String) As String
Dim i As Long
For i = 1 To Len(S)
    If Mid(S, i, 1) Like "[1-9]" Then GetNumbers = GetNumbers & Mid(S, i, 1)
Next i
End Function

Your formula in the cell could then reference that function.

That does not work for me.

 

[admiral100]

Justinian , What do you mean by saying second tab?

 

[Justinian]

Sheet1 is the input and Sheet2 is where all the data (suite numbers and room counts) are stored.

 

[admiral100]

Let's say :

Sheet1 A1 = 3302F
Sheet1 B1 = Twr 3sig

Sheet2 A23 = 3302F
Sheet2 B23 =(LOOKUP(9^9,0+MID(VLOOKUP(A23,Sheet1!A1:B1,2),MIN(FIND({0,1,2,3,4,5,6,7,8,9},VLOOKUP(A23,Sheet1!A1:B1,2)&1/19)),ROW($1:$16))))