software

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
www.officekb.com/Uwe/Forums.a...excel/200604/1

If your data always ends with quot;yearsquot; or quot; yearsquot;, you could just select that
column, then

Edit|replace
what: year
with: (leave blank)
replace all
quot;Fam via OfficeKB.comquot; wrote:
gt;
gt; Good Morning ...
gt; if I have a cell with alpha numeric value, is there any way to extract only
gt; numeric value out of the cell?
gt; for example:
gt; 9 years
gt; 22 years
gt; 20.5 years
gt; 22.17years
gt; 22.17 years
gt; can it be just after some formula:
gt; 9
gt; 22
gt; 20.5
gt; 22.17
gt; 22.17
gt; Any help will be appreciated.
gt; thx
gt;
gt; --
gt; Message posted via OfficeKB.com
gt; www.officekb.com/Uwe/Forums.a...excel/200604/1

--

Dave Peterson

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 Studentquot;Fam via OfficeKB.comquot; wrote:

gt; Good Morning ...
gt; if I have a cell with alpha numeric value, is there any way to extract only
gt; numeric value out of the cell?
gt; for example:
gt; 9 years
gt; 22 years
gt; 20.5 years
gt; 22.17years
gt; 22.17 years
gt; can it be just after some formula:
gt; 9
gt; 22
gt; 20.5
gt; 22.17
gt; 22.17
gt; Any help will be appreciated.
gt; thx
gt;
gt; --
gt; Message posted via OfficeKB.com
gt; www.officekb.com/Uwe/Forums.a...excel/200604/1
gt;

Make sure you include the quot;Squot;

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

Dave Peterson wrote:
gt;
gt; If your data always ends with quot;yearsquot; or quot; yearsquot;, you could just select that
gt; column, then
gt;
gt; Edit|replace
gt; what: year
gt; with: (leave blank)
gt; replace all
gt;
gt; quot;Fam via OfficeKB.comquot; wrote:
gt; gt;
gt; gt; Good Morning ...
gt; gt; if I have a cell with alpha numeric value, is there any way to extract only
gt; gt; numeric value out of the cell?
gt; gt; for example:
gt; gt; 9 years
gt; gt; 22 years
gt; gt; 20.5 years
gt; gt; 22.17years
gt; gt; 22.17 years
gt; gt; can it be just after some formula:
gt; gt; 9
gt; gt; 22
gt; gt; 20.5
gt; gt; 22.17
gt; gt; 22.17
gt; gt; Any help will be appreciated.
gt; gt; thx
gt; gt;
gt; gt; --
gt; gt; Message posted via OfficeKB.com
gt; gt; www.officekb.com/Uwe/Forums.a...excel/200604/1
gt;
gt; --
gt;
gt; Dave Peterson

--

Dave Peterson

Thanks, it works great
Fam

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

--
Message posted via www.officekb.com

Another way.

=--LEFT(F1,FIND(quot; quot;,F1))

HTH
Regards,
Howard

quot;Fam via OfficeKB.comquot; lt;u18245@uwegt; wrote in message
news:5f5bbbab4e0ec@uwe...
gt; Good Morning ...
gt; if I have a cell with alpha numeric value, is there any way to extract
gt; only
gt; numeric value out of the cell?
gt; for example:
gt; 9 years
gt; 22 years
gt; 20.5 years
gt; 22.17years
gt; 22.17 years
gt; can it be just after some formula:
gt; 9
gt; 22
gt; 20.5
gt; 22.17
gt; 22.17
gt; Any help will be appreciated.
gt; thx
gt;
gt; --
gt; Message posted via OfficeKB.com
gt; www.officekb.com/Uwe/Forums.a...excel/200604/1