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!

 

[Ingolf]

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

[TABLE="width: 500"]
<tbody>[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
  Next[B][COLOR=#FF0000]ThTT[/COLOR][/B]
End Function
[/TD]
[/TR]
</tbody>[/TABLE]

What is that ThTT in red in your function??
If remove that ThTT, result number of your function, are negative numbers.
Please check.

 

[Rick Rothstein]

What is that ThTT in red in your function??
If remove that ThTT, result number of your function, are negative numbers.
Please check.

I have this Logitech device which allows me to share one keyboard and one mouse between two computers. It is supposed to switch automatically between the computers depending on which cursor is active. I half remember (before going to sleep) that I thought I was typing on one computer and saw nothing and then my keystrokes went to the computer I wanted. I looked at the computer running Excel but did not see the errant keystrokes... apparently the ThTT was them. Anyway, removing those letters (which I'll do below to keep a "clean" thread) makes the code work correctly for me. As a matter of fact, I can see no physical way this code could produce a negative number as a result.

Here is corrected code for what I originally posted in Message #9 , so you can ignore the code I posted there, but the write up there still applies.

[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
  Next
End Function[/td]
[/tr]
[/table]

 

[Ingolf]

As a matter of fact, I can see no physical way this code could produce a negative number as a result.​

You're right. Somehow the minus sign appeared in front of the LeftNum( ...

Thank tou.