Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Parsing a alpha character out of a cell | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Combining data (numeric format) in multiple cells into one cell (t | Excel Discussion (Misc queries) | |||
resolving a numeric cell entry for its meaning | Excel Worksheet Functions |