Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Ignore text Function
Is there function that will look at the value of a cell and ignore the text
but return the numeric value only. Example: cell value is CH123XX Retrun value : 123 Amount of text would be dynamic. Thnanks |
#2
|
|||
|
|||
see the 2nd formula in Peo's post here;
http://groups-beta.google.com/group/...2?dmode=source "Grant" wrote in message ... Is there function that will look at the value of a cell and ignore the text but return the numeric value only. Example: cell value is CH123XX Retrun value : 123 Amount of text would be dynamic. Thnanks |
#3
|
|||
|
|||
Hi Grant
One way, although it is a bit long winded, is to use Data/Text to Columns. and then delete the columns you don't need. You could then concatenate the columns that are left, back to one cell. Using =LEFT() and =MID() functions will also do the trick, but if the length of the string is dynamic, it may cause some serious errors. HTH Michael "Grant" wrote: Is there function that will look at the value of a cell and ignore the text but return the numeric value only. Example: cell value is CH123XX Retrun value : 123 Amount of text would be dynamic. Thnanks |
#4
|
|||
|
|||
If the numbers are always together in the string, you
could use this formula (array-entered) =MID(A1,MATCH(TRUE,bre,0),MATCH(TRUE,bre)-MATCH (TRUE,bre,0)+1) where bre is a defined name for: =ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)) If not, Gord Dibben posted a robutst macro solution: http://tinyurl.com/5p5ms HTH Jason Atlanta, GA -----Original Message----- Is there function that will look at the value of a cell and ignore the text but return the numeric value only. Example: cell value is CH123XX Retrun value : 123 Amount of text would be dynamic. Thnanks . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I combine text and a function in a single cell | Excel Worksheet Functions | |||
How do I add help text to an excel function I have created | Excel Worksheet Functions | |||
Convert Numeric into Text | Excel Worksheet Functions | |||
Macro or Function to make text size to suite text Length? | Excel Discussion (Misc queries) | |||
Excel IF function for text | Excel Worksheet Functions |