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!

 

[etaf]

is there always a space between the number and yrs
does it always have yrs at the end

you could look for the space or use the length of text

=Left ( A1, find(" ", A1, 1)-1)

Book2
	A	B
1	1 yrs	1
2	10 yrs	10
3	100 yrs	100
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=LEFT(A1,FIND(" ",A1,1)-1)

OR
=LEFT(A1,LEN(A1)-4)

Book2
	A	B
1	1 yrs	1
2	10 yrs	10
3	100 yrs	100
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=LEFT(A1,LEN(A1)-4)

That will result in a TEXT number so to change to a real
multiply by 1

=Left ( A1, find(" ", A1, 1)-1) * 1
OR
=LEFT(A1,LEN(A1)-4) *1

Book2
	A	B	C
1	1 yrs	1	1
2	10 yrs	10	10
3	100 yrs	100	100
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=LEFT(A1,LEN(A1)-4)*1
C1:C3	C1	=LEFT(A1,LEN(A1)-4)*1

 

[lrobbo314]

A VBA solution.

Book1 (version 1).xlsb
	A	B
1	1 years	1
2	2 years	2
3	3 years	3
4	4 years	4
5	5 years	5
6	6 years	6
7	7 years	7
8	8 years	8
9	9 years	9
10	10 years	10
11	20 years	20
12	50 years	50
13	100 years	100
14	500 years	500
15	1000 years	1000
16	sdfjsi	#N/A
17	x 314 yrs	314
Sheet9

Cell Formulas
Range	Formula
B1:B17	B1	=OnlyNumbers(A1)

Function OnlyNumbers(s As String)
Dim b() As Byte:    b = s
Dim tmp As String

For i = 0 To UBound(b) Step 2
    tmp = b(i)
    If tmp > 47 And tmp < 58 Then OnlyNumbers = OnlyNumbers & Chr(tmp)
Next i

If Len(OnlyNumbers) > 0 Then
    OnlyNumbers = Int(OnlyNumbers)
Else
    OnlyNumbers = "#N/A"
End If
End Function

 

[Rick Rothstein]

@lrobbo314... You function returns all numbers in a cell as opposed to the first number. I am thinking of something like this....

Joe is 34 years 5 months old

Your function returns 345 for that text. The OP was not clear if there could be multiple numbers in a cell and what should be done if there are, so your code may, in fact, be what the OP actually wants. I assumed if there were multiple numbers in the cell, the OP would only want the first one. Here is my version of your OnlyNumbers function that works this way...

Function OnlyNumbers(s As String) As Variant
  If s Like "*#*" Then
    If Left(s, 1) Like "#" Then
      OnlyNumbers = Val(s)
    Else
      OnlyNumbers = Val(Mid(s, Evaluate("MIN(FIND({0,1,2,3,4,5,6,7,8,9},""" & s & """&""0123456789""))")))
    End If
  Else
    OnlyNumbers = "#N/A"
  End If
End Function

 

[Rick Rothstein]

By the way, if the values the OP needs to parse always begin with the number he wants to retrieve (as his short description kind of indicates), then my function's code can be simplified like this...

Function OnlyNumbers(s As String) As Variant
  If s Like "#*" Then
    If Left(s, 1) Like "#" Then OnlyNumbers = Val(s)
  Else
    OnlyNumbers = "#N/A"
  End If
End Function

 

[lrobbo314]

@Rick Rothstein , is there a formula only version of the your VBA Min(Find....?

 

[Rick Rothstein]

The following formula returns the location of the first digit in the text in cell A1, is that what you were asking for?

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))

 

[lrobbo314]

This is the hiccup I was running into. It just returns the 1st digit, not the entire number. ONU() is the function you provided earlier.

Book1 (version 1).xlsb
	A	B	C	D	E
18	f 34 and 4		34		3
Sheet9

Cell Formulas
Range	Formula
C18	C18	=onu(A18)
E18	E18	=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A18&"0123456789"))

 

[Rick Rothstein]

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)))

 

[lrobbo314]

Cool, thanks. Never really used this kind of formula, but seeing your VBA solution sparked an interest for the formula version.