| Home |
| Search |
| Today's Posts |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
With your text in cell A1; try this formula in B1 .
Hi Jacob thanks a lot u r Genius man thx so much
On Friday, August 21, 2009 12:37 PM novastar wrote: Hi, I have a column of cells which are in any of the following formats: x # x x # x x x # where x could be any number of characters (a-z or A-Z) either upper or lower case. I generally have to post process this file in UNIX before I import it into excel and do some modifications. Is there an easier way to do this in Excel? I do not mind copying the column and post processing one column to retain numbers and another column to retain text. -- novastar On Friday, August 21, 2009 12:44 PM Jacob Skaria wrote: With your text in cell A1; try this formula in B1 .Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1) ),0),99) If this post helps click Yes --------------- Jacob Skaria "novastar" wrote: On Friday, August 21, 2009 12:54 PM T. Valko wrote: This will extract the last "word" in a cell: =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255)) Note that the result is a *TEXT* string even if the last "word" in the cell is a number. If your numbers can contain leading 0s then you will need to extract them as text in order to retain the leading 0s. -- Biff Microsoft Excel MVP On Friday, August 21, 2009 1:03 PM Jacob Skaria wrote: The previous one will return an error for a lenghty string. Try this array formula =MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$99),1 )),0),99) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: On Friday, August 21, 2009 1:31 PM novastar wrote: Hi T.Valco, Thanks........this helps me get the numbers in a column.. Now....I would like to eliminate the numbers and extract only the text in a different column...how do i do this? -- novastar "T. Valko" wrote: On Friday, August 21, 2009 1:36 PM Jacob Skaria wrote: In B1 =SUBSTITUTE(A1,C1,) In C1 =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)) If this post helps click Yes --------------- Jacob Skaria "novastar" wrote: On Friday, August 21, 2009 1:41 PM T. Valko wrote: Let's assume you have this formula to extract the numbers in cell B1: =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255)) Enter this formula in C1 to return only the text portion of cell A1: =TRIM(SUBSTITUTE(A1,B1,"")) -- Biff Microsoft Excel MVP On Friday, August 21, 2009 1:44 PM novastar wrote: Hi Jacob, This did not work for me. If I have a cell containing "Adam 22", I would like to separate "adam" in one column and "22" in another column. With the formula below I get "#N/A" in the cell. -- novastar "Jacob Skaria" wrote: On Friday, August 21, 2009 1:49 PM Jacob Skaria wrote: You are entering the formula as normal. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "novastar" wrote: On Friday, August 21, 2009 1:57 PM novastar wrote: Hi Jacob, Thanks....this helps. Thanks to both Jacob and T.Valko...you helped me fix an issue I have been struggling with for years. Now I don;t have to go to UNIX and stay in excel...:-) Thanks again. -- novastar "Jacob Skaria" wrote: On Friday, August 21, 2009 2:08 PM JP Ronse wrote: Hi, You could try 'text to columns' ... and save it as csv. But if you have to post-process it with unix, why do not making a shell script that does it all? I suppose it is a 'text' file like most unix files a - sed 's/ /,/g' file1 file2 will replace the spaces by "," Keep in mind that Unix is 7-bit oriented ans Windows/Dos is 8: dos2unix file file1 ( I think, use man dos2unix in unix environment) What is the added value of post processing in Unix? If you do not mind to send me a sample and explaining what your outcome must be, I will have a look into. Wkr, JP On Friday, August 21, 2009 2:13 PM novastar wrote: Hi Jacob, My bad. I was wrong. I did what you said and it works just fine. Thanks much! -- novastar "Jacob Skaria" wrote: On Friday, August 21, 2009 10:04 PM Ron Rosenfeld wrote: If the text always comes first, and the number last, then For text: =TRIM(LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1 ,2,3,4,5,6,7,8,9,0"))-1)) For the numeric portion (with the number returned as text, which would include leading zero's): =MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4 ,5,6,7,8,9,0")),99) or, to return the value as numeric: =--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4, 5,6,7,8,9,0")),99) --ron |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to separate text and numbers | Excel Discussion (Misc queries) | |||
| move text & numbers in column to separate columns | Excel Discussion (Misc queries) | |||
| Separate Text into numbers and alphabets | Excel Worksheet Functions | |||
| how do i separate numbers and text in a cell? | Excel Discussion (Misc queries) | |||
| How to separate numbers from text?? | Excel Discussion (Misc queries) | |||
Threaded Mode
