only extract numeric value from alpha numeric cell
 

Good Morning ...
if I have a cell with alpha numeric value, is there any way to extract only
numeric value out of the cell?
for example:
9 years
22 years
20.5 years
22.17years
22.17 years
can it be just after some formula:
9
22
20.5
22.17
22.17
Any help will be appreciated.
thx

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200604/1

only extract numeric value from alpha numeric cell
 

If your data always ends with "years" or " years", you could just select that
column, then

Edit|replace
what: year
with: (leave blank)
replace all



"Fam via OfficeKB.com" wrote:

Good Morning ...
if I have a cell with alpha numeric value, is there any way to extract only
numeric value out of the cell?
for example:
9 years
22 years
20.5 years
22.17years
22.17 years
can it be just after some formula:
9
22
20.5
22.17
22.17
Any help will be appreciated.
thx

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200604/1

--

Dave Peterson

only extract numeric value from alpha numeric cell
 

For cells like 1.234 years you want to discard the last 6 characters, use:
=LEFT(A1,LEN(A1)-6)
For cells like 1.234years you want to discard the last 5 characters, use:
=LEFT(A1,LEN(A1)-5)
--
Gary's Student


"Fam via OfficeKB.com" wrote:

Good Morning ...
if I have a cell with alpha numeric value, is there any way to extract only
numeric value out of the cell?
for example:
9 years
22 years
20.5 years
22.17years
22.17 years
can it be just after some formula:
9
22
20.5
22.17
22.17
Any help will be appreciated.
thx

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200604/1

only extract numeric value from alpha numeric cell
 

Make sure you include the "S"

Edit|replace
what: years <--- changed
with: (leave blank)
replace all

Dave Peterson wrote:

If your data always ends with "years" or " years", you could just select that
column, then

Edit|replace
what: year
with: (leave blank)
replace all

"Fam via OfficeKB.com" wrote:

Good Morning ...
if I have a cell with alpha numeric value, is there any way to extract only
numeric value out of the cell?
for example:
9 years
22 years
20.5 years
22.17years
22.17 years
can it be just after some formula:
9
22
20.5
22.17
22.17
Any help will be appreciated.
thx

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200604/1

--

Dave Peterson

--

Dave Peterson

only extract numeric value from alpha numeric cell
 

Thanks, it works great
Fam

Gary''s Student wrote:
For cells like 1.234 years you want to discard the last 6 characters, use:
=LEFT(A1,LEN(A1)-6)
For cells like 1.234years you want to discard the last 5 characters, use:
=LEFT(A1,LEN(A1)-5)
Good Morning ...
if I have a cell with alpha numeric value, is there any way to extract only
[quoted text clipped - 13 lines]
Any help will be appreciated.
thx

--
Message posted via http://www.officekb.com

only extract numeric value from alpha numeric cell
 

Another way.

=--LEFT(F1,FIND(" ",F1))

HTH
Regards,
Howard

"Fam via OfficeKB.com" <u18245@uwe wrote in message
news:5f5bbbab4e0ec@uwe...
Good Morning ...
if I have a cell with alpha numeric value, is there any way to extract
only
numeric value out of the cell?
for example:
9 years
22 years
20.5 years
22.17years
22.17 years
can it be just after some formula:
9
22
20.5
22.17
22.17
Any help will be appreciated.
thx

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200604/1