Removing Part of Cell/Text

[RJB78]

Hello - Trying to come up with formula that will give me the number from a different cell, but the cell I am trying to reference also had letters,

Example: The cell referenced, lets call it A1, contains: '4 yrs'. I would like cell A2 to calculate 4. I tried doing a =LEFT(A1,1), but the issue I ran into was double digit numbers ie 10 yrs., would only return the first digit, 1.

Thank you in advance!

 

[RJB78]

Thank you all, very helpful.

 

[Peter_SSs]

Is this what you are looking for...

=LOOKUP(9.9E+307,--LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99),ROW($1:$99)))

This is not very robust though. In the examples below ..

• The first example fails if a couple of rows are inserted above
• The second one fails outright

The first issue is easy enough to resolve, but the second is not so simple to solve with worksheet formulas. For one thing, whether decimal numbers might be included or not could change the approach.

21 09 18.xlsm
	A	B
1	a 5 b 3	5
2	xx 1 decade	44531
Sample

Cell Formulas
Range	Formula
B1:B2	B1	=LOOKUP(9.9E+307,--LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99),ROW($1:$101)))

 

[JEC]

With regex. Catches decimal numbers as well.
Did not manage to put it in one pattern, which is probably possible.

Function jec(cell As String) As Double
 On Error Resume Next
  With CreateObject("VBscript.RegExp")
    .Global = True
    .Pattern = "\d+\.(\d+)"
     a = .Execute(cell)(0)
    .Pattern = "\d+"
     If IsEmpty(a) Then
        jec = .Execute(cell)(0)
     Else
        jec = a
     End If
  End With
End Function

 

[Peter_SSs]

Did not manage to put it in one pattern, which is probably possible.

It certainly is, also catches negative numbers & handles if no numbers at all

Function GetNum(s As String) As Variant
  Dim RX As Object
  
  GetNum = ""
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "\-?\d+(\.\d*)?"
  If RX.Test(s) Then GetNum = Val(RX.Execute(s)(0))
End Function

21 09 18.xlsm
	A	B
1	a 5 b 3	5
2	xx 1 decade	1
3
4	a 1.2356	1.2356
5	avv -67.1434 abc	-67.1434
6	xxx
Sample

Cell Formulas
Range	Formula
B1:B6	B1	=GetNum(A1)

 

[JEC]

Nice! I was so close