Extract First Digit from Alphanumeric Number

[vikrampnz]

Hello

I have a list of alphanumeric values in a column as below:

B-101
B-102
B-103
B-904
B-806
B-1005
B-1103
and so on. Effectively, these represent floor numbers... i.e. 101 means "1" floor, 1103 means "11" Floor etc. I want to automatically get floor number next to the unit numbers in a separate column.

Is there a formula in excel where I can extract first digit from a 3 digit alphanumeric value e.g. B-904 - I want to extract "9" in the adjacent column. Similar first 2 digits from a value with 4 digits e.g. B-1005 - I want to extract "10". In other words, I want to extract the digit/s excluding the last two digits.

I have tried the formula to convert the Alphanumeric value to Number i.e. I can convert B-1005 to 1005 using the formula:

=RIGHT(A2, LEN(A2) - SEARCH("-", A2)) *1

. But unable to differentiate between 1,10 & 11.

I would appreciate any help offered.

 

[Sufiyan97]

Are you still using Excel 2007?

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2	B-101	1
3	B-102	1
4	B-103	1
5	B-904	9
6	B-806	8
7	B-1005	10
8	B-1103	11
Sheet5

Cell Formulas
Range	Formula
B2:B8	B2	=MID(A2,3,LEN(A2)-4)

 

[johnnyL]

Slightly longer formula but a bit more flexible:

Book1
	A	B	C
1	B-101	1
2	B-102	1
3	B-103	1
4	B-904	9
5	B-806	8
6	B-1005	10
7	B-1103	11
8	AB-142	1
9	ABC-1142	11
10
Sheet1

Cell Formulas
Range	Formula
B1:B9	B1	=MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1)-2)

 

[alansidman]

An alternative with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom Column" = Table.AddColumn(Source, "Custom", each let splitColumn1 = Splitter.SplitTextByDelimiter("-", QuoteStyle.None)([Column1]) in Text.Reverse(Text.Middle(Text.Reverse(splitColumn1{1}?), 2)), type text)
in
    #"Added Custom Column"

 

[johnnyL]

@alansidman I don't believe Power Query is available for Excel 2007 that the OP is showing as the current Excel version.

 

[bebo021999]

Or this:

=INT(MID(A2,SEARCH("-",A2)+1,10)/100)

 

[alansidman]

@johnnyL
True, and yet the OP has not acknowledged any post here and in particular the question asked in Post #2 by Sufiyan97. The OP has been a member here since 2006. Perhaps he has upgraded.