Extracting left most numbers from excel

[dlsdioquino]

Hello All
I need to extract the left most numbers in a cell.
Here is an example:

CELL EXTRACT
ASD-12 12
ASD-12-D-123 12
ASD-1-2-3 1

I already have this formula but it extracts all numbers

=SUM(MID(0&A5,LARGE(ISNUMBER(--MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1))*ROW(INDIRECT("1:"&LEN(A5))),ROW(INDIRECT("1:"&LEN(A5))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A5)))/10)

Would appreciate your help. Thank you!

 

[Peter_SSs]

Welcome to the MrExcel board!

Based on that sample data, this should do it.

=REPLACE(LEFT(A2,FIND("-",A2&"-",5)-1),1,4,"")+0

Edit: Or even ..
=MID(A2,5,FIND("-",A2&"-",5)-5)+0


If that is not it, please give a more representative set of sample data and expected results.

 

[dlsdioquino]

Sorry I also noticed that I put a sample size with all the same format. Here are more sample data with the expected results:

QLD PO-Q160041A

160041

<tbody>
</tbody>

HMK-323-MANA-01	323
HMK-743-MS1500-01	743

<colgroup><col><col></colgroup><tbody>
</tbody>

WD PO-15471, ORDER C

15471

<tbody>
</tbody>

Westrac 777G-PI-IS

777

<tbody>
</tbody>

 

[Peter_SSs]

Here are more sample data with the expected results:

Much more useful!

You could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function LeftNum(s As String) As Long
  Static RX As Object
  
  If RX Is Nothing Then Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "\d+"
  LeftNum = RX.Execute(s)(0)
End Function

Excel Workbook
	A	B
2	ASD-12	12
3	ASD-12-D-123	12
4	ASD-1-2-3	1
5	QLD PO-Q160041A	160041
6	HMK-323-MANA-01	323
7	HMK-743-MS1500-01	743
8	WD PO-15471, ORDER C	15471
9	Westrac 777G-PI-IS	777
Sheet1

 

[dlsdioquino]

Thank you so much!

 

[Peter_SSs]

Thank you so much!

Glad to help.

 

[dlsdioquino]

Upon testing other data I've stumbled upon some fields that would not seem to work such as:

BAR PO-4500016742 BHP PO- 4504238970 3004544191 <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

Sorry to keep on asking, I'm really new to excel and had no experience with it.

 

[István Hirsch]

If you need a formula solution give this a try:

Excel Workbook
	A	B
1	QLD PO-Q160041A	160041
2
3	HMK-323-MANA-01	323
4	HMK-743-MS1500-01	743
5
6
7	WD PO-15471, ORDER C	15471
8
9	Westrac 777G-PI-IS	777
10	BAR PO-4500016742	4500016742
11	BHP PO- 45042389703004544191	4.50424E+19
Sheet1

 

[Rick Rothstein]

The following function will return numbers up to 28 digits long, but those numbers will be text numbers, not real numbers.

[table="width: 500"]
[tr]
	[td]Function LeftNum(ByVal S As String) As Variant
  Dim X As Long, Z As Long
  For X = 1 To Len(S)
    If IsNumeric(Mid(S, X, 1)) Then
      S = Mid(S, X) & "X"
      For Z = 1 To Len(S)
        If Not IsNumeric(Mid(S, Z, 1)) Then
          LeftNum = Format$(Left(S, Z - 1), "############################")
          Exit Function
        End If
      Next
    End If
  NextThTT
End Function[/td]
[/tr]
[/table]

 

[Peter_SSs]

Upon testing other data I've stumbled upon some fields that would not seem to work such as:

BAR PO-4500016742 BHP PO- 4504238970 3004544191 <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

It is to do with with the length of those numbers. If you need the results to be numbers, try changing the first line of the function to

Function LeftNum(s As String) As Double

If you are happy to have the results as text strings then

Function LeftNum(s As String) As String