# Removing Part of Cell/Text

#### RJB78

##### Board Regular
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.

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### etaf

##### Well-known Member
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
AB
11 yrs1
210 yrs10
3100 yrs100
Sheet1
Cell Formulas
RangeFormula
B1:B3B1=LEFT(A1,FIND(" ",A1,1)-1)

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

Book2
AB
11 yrs1
210 yrs10
3100 yrs100
Sheet1
Cell Formulas
RangeFormula
B1:B3B1=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
ABC
11 yrs11
210 yrs1010
3100 yrs100100
Sheet1
Cell Formulas
RangeFormula
B1:B3B1=LEFT(A1,LEN(A1)-4)*1
C1:C3C1=LEFT(A1,LEN(A1)-4)*1

#### lrobbo314

##### Well-known Member
A VBA solution.

Book1 (version 1).xlsb
AB
11 years1
22 years2
33 years3
44 years4
55 years5
66 years6
77 years7
88 years8
99 years9
1010 years10
1120 years20
1250 years50
13100 years100
14500 years500
151000 years1000
16sdfjsi#N/A
17x 314 yrs314
Sheet9
Cell Formulas
RangeFormula
B1:B17B1=OnlyNumbers(A1)

VBA Code:
``````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

##### MrExcel MVP
@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...
VBA Code:
``````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

##### MrExcel MVP

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...
VBA Code:
``````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

##### Well-known Member
@Rick Rothstein , is there a formula only version of the your VBA Min(Find....?

#### Rick Rothstein

##### MrExcel MVP

The following formula returns the location of the first digit in the text in cell A1, is that what you were asking for?
Excel Formula:
``=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))``

#### lrobbo314

##### Well-known Member
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
ABCDE
18f 34 and 4343
Sheet9
Cell Formulas
RangeFormula
C18C18=onu(A18)
E18E18=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A18&"0123456789"))

#### Rick Rothstein

##### MrExcel MVP
Is this what you are looking for...
Excel Formula:
``=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

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

Replies
5
Views
158
Replies
2
Views
89
Replies
5
Views
107
Replies
4
Views
96
Replies
2
Views
226

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,461
Messages
5,831,778
Members
430,089
Latest member
Jane434

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back