Removing Part of Cell/Text

RJB78

Board Regular
Joined
Aug 8, 2016
Messages
64
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

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
Joined
Oct 24, 2012
Messages
5,617
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Jul 14, 2008
Messages
3,434
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 18, 2011
Messages
37,866
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
@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
Joined
Apr 18, 2011
Messages
37,866
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,866
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jul 14, 2008
Messages
3,434
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 18, 2011
Messages
37,866
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Jul 14, 2008
Messages
3,434
Office Version
  1. 365
Platform
  1. Windows
Cool, thanks. Never really used this kind of formula, but seeing your VBA solution sparked an interest for the formula version.
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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
Top