Removing Part of Cell/Text

RJB78

Board Regular
Joined
Aug 8, 2016
Messages
69
Office Version
  1. 365
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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
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
 
Upvote 0
@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
 
Upvote 0
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
 
Upvote 0
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"))
 
Upvote 0
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"))
 
Upvote 0
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)))
 
Upvote 0
Cool, thanks. Never really used this kind of formula, but seeing your VBA solution sparked an interest for the formula version.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top