extract leading numbers from a text string

[Pauleastgrinstead]

I only dip into Excel on an occasional basis and have had little experience of string handling. There are some very complicated functions out there! I need a formula to extract leading numbers from a text string into a new cell. There could be a variable number of leading numbers. Generally there will only be 1 or 2 leading numbers

Input Output
7K1 7
10X2 10

 

[Pauleastgrinstead]

I have just found on another thread the formula

=LOOKUP(2,1/--LEFT(A2,ROW($1:$3)),--LEFT(A2,ROW($1:$3)))

which works for up to 3 leading numbers.
would anyone care to explain how this works!

 

[Misca]

Welcome to the message board!

Two different approaches come to mind. The first one is quick and dirty and only works when there's no more than 2 leading numbers. The second one is quite a bit more complex but should work with much longer leading numbers (and needs to be entered with Ctrl + Shift + Enter instead of just Enter).

Excel Workbook
	A	B
1	7k	7
2	10X10	10
Taul3

Both formulas return errors if there's no leading numbers.

 

[Pauleastgrinstead]

I understand the first one and that should be perfectly adequate for my needs. Many thanks!

 

[Pauleastgrinstead]

I am trying to convert the formula

=IFERROR(LEFT(A1,2)*1,LEFT(A1,1)*1) into a macro but am getting an error

so far I have written :
Public Function StudentYear(StudentForm As String) As String

Dim Year As String
Year = WorksheetFunction.IfError(Left(StudentForm, 2) * 1, Left(StudentForm, 1) * 1)
StudentYear = Year

End Function

This works for input data of format 77Y1, but not for 7Y1 (1 leading number) - I get a #VALUE! error.
Anybody help please?

 

[Rick Rothstein]

I am trying to convert the formula

=IFERROR(LEFT(A1,2)*1,LEFT(A1,1)*1) into a macro but am getting an error

so far I have written :

Actually, for the data structure used in the original post, neither posted formula solution works for all values. For example, they will both fail for this...

2E4ABC

This array-entered** formula would be the foolproof formula to use...

=LEFT(A1,MATCH(TRUE,ISERROR(1*LEFT(SUBSTITUTE(A1," ","X")&"X",ROW(INDEX(A:A,1):INDEX(A:A,99)))),0)-1)

**Commit this formula using CTRL+SHIFT+ENTER, not just Enter by itself

Now, for a UDF solution (instead of a macro), there is no need to try and use any worksheet formula solutions.

Function LeadingNumber(S As String) As Double
  LeadingNumber = Val(Replace(Replace(S, "E", "X", , , 1), "D", "X", , , 1))
End Function

 

[Pauleastgrinstead]

Rick,
Thank you for the UDF. However I am very unfamiliar with the replace function as used here. Can you explain to me the significance of the "E" "X" and "D" "X". A full English explanation of what is going on in the UDF would be very much appreciated.
Paul

 

[Rick Rothstein]

Rick,
Thank you for the UDF. However I am very unfamiliar with the replace function as used here. Can you explain to me the significance of the "E" "X" and "D" "X". A full English explanation of what is going on in the UDF would be very much appreciated.

The Val function will return the leading number from a text string, which is what you requested... however, it has the same flaw that the other formula I mentioned does... it will see the E (upper or lower case) and, unlike the formulas, the D (upper or lower case) as a power of 10 indicator, so it would see 12e4abc as 12e4 which is 120000, not 12... so, what I do is use the Replace function to replace the E or D with an X which would change the 12e4abc example to 12X4abc... now Val only sees the 12. Inside each Replace function, you will see 3 commas and a 1... the commas are to skip over arguments of Replace that I do not want to change from their default, the 1 tells Replace to consider the text to be replaced (for your function, that is either the E or D) as either upper or lower case (so that e and d are also replaced.

 

[Pauleastgrinstead]

Brilliant Rick, I had just got to the point of realising that the E and D could be in numeric formats and cause a problem unless replaced.