| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Stripping nubers from text in a cell
I have a bunch of cell with varying lench that have numbers at the beginning
(also of varying length. Ex: 1258 ahjKJ yuUIOI 45687045HJKoiuwER I just want to extract the text from these cell. How do I do that? Thanks, Dan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripping nubers from text in a cell
Assuming your text is not longer than 999 characters and that your digits
are all next to each other (no leading or intervening spaces or characters)... =MID(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))+1,999) Rick "Dan S" wrote in message ... I have a bunch of cell with varying lench that have numbers at the beginning (also of varying length. Ex: 1258 ahjKJ yuUIOI 45687045HJKoiuwER I just want to extract the text from these cell. How do I do that? Thanks, Dan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripping nubers from text in a cell
With
A1: (containing text that may begin with numbers.....eg 1258 ahjKJ yuUIOI) This formula (in sections to compensate for text wrap) returns the text to the right of those beginning numbers: B1: =MID(A1,MATCH(FALSE,INDEX(ISNUMBER(--LEFT(A1,ROW(INDEX($A:$A,1): INDEX($A:$A,LEN(A1))))),0),0),LEN(A1)) In the above example, B1 returns: ahjKJ yuUIOI Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Dan S" wrote in message ... I have a bunch of cell with varying lench that have numbers at the beginning (also of varying length. Ex: 1258 ahjKJ yuUIOI 45687045HJKoiuwER I just want to extract the text from these cell. How do I do that? Thanks, Dan |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripping nubers from text in a cell
*Maybe* this:
Assuming the number is *always* at the beginning of the string and that there are no other numbers in the string. =TRIM(MID(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))+1,25 5)) -- Biff Microsoft Excel MVP "Dan S" wrote in message ... I have a bunch of cell with varying lench that have numbers at the beginning (also of varying length. Ex: 1258 ahjKJ yuUIOI 45687045HJKoiuwER I just want to extract the text from these cell. How do I do that? Thanks, Dan |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripping nubers from text in a cell
Certainly better than what I threw together.
(I don't know WHAT I was thinking.) Ron "Rick Rothstein (MVP - VB)" wrote in message ... Assuming your text is not longer than 999 characters and that your digits are all next to each other (no leading or intervening spaces or characters)... =MID(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))+1,999) Rick "Dan S" wrote in message ... I have a bunch of cell with varying lench that have numbers at the beginning (also of varying length. Ex: 1258 ahjKJ yuUIOI 45687045HJKoiuwER I just want to extract the text from these cell. How do I do that? Thanks, Dan |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripping nubers from text in a cell
Needs to trim the space in the first example.
-- Biff Microsoft Excel MVP "Ron Coderre" wrote in message ... Certainly better than what I threw together. (I don't know WHAT I was thinking.) Ron "Rick Rothstein (MVP - VB)" wrote in message ... Assuming your text is not longer than 999 characters and that your digits are all next to each other (no leading or intervening spaces or characters)... =MID(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))+1,999) Rick "Dan S" wrote in message ... I have a bunch of cell with varying lench that have numbers at the beginning (also of varying length. Ex: 1258 ahjKJ yuUIOI 45687045HJKoiuwER I just want to extract the text from these cell. How do I do that? Thanks, Dan |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripping nubers from text in a cell
Maybe this (If no other numbers will be embedded within the text):
=MID(A1,COUNT(INDEX(--MID(A1,ROW($1:$255),1),0))+1,LEN(A1)) or...if you need to remove leading/trailing spaces: =TRIM(MID(A1,COUNT(INDEX(--MID(A1,ROW($1:$255),1),0))+1,LEN(A1))) But, if there may be numbers in the text...the hideous formula I posted earlier will do what you asked for. Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Ron Coderre" wrote in message ... With A1: (containing text that may begin with numbers.....eg 1258 ahjKJ yuUIOI) This formula (in sections to compensate for text wrap) returns the text to the right of those beginning numbers: B1: =MID(A1,MATCH(FALSE,INDEX(ISNUMBER(--LEFT(A1,ROW(INDEX($A:$A,1): INDEX($A:$A,LEN(A1))))),0),0),LEN(A1)) In the above example, B1 returns: ahjKJ yuUIOI Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Dan S" wrote in message ... I have a bunch of cell with varying lench that have numbers at the beginning (also of varying length. Ex: 1258 ahjKJ yuUIOI 45687045HJKoiuwER I just want to extract the text from these cell. How do I do that? Thanks, Dan |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripping nubers from text in a cell
On Thu, 6 Mar 2008 18:01:32 -0500, "Rick Rothstein \(MVP - VB\)"
wrote: =MID(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))+1,999) Slightly shorter <vbg =MID(A1,SUMPRODUCT(--ISNUMBER(-MID(A1,ROW($1:$999),1)))+1,999) --ron |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripping nubers from text in a cell
I think that might do the trick...I wish there was something a little
simplier (built in the Excel) but I appreciate the help! Dan "Ron Coderre" wrote: With A1: (containing text that may begin with numbers.....eg 1258 ahjKJ yuUIOI) This formula (in sections to compensate for text wrap) returns the text to the right of those beginning numbers: B1: =MID(A1,MATCH(FALSE,INDEX(ISNUMBER(--LEFT(A1,ROW(INDEX($A:$A,1): INDEX($A:$A,LEN(A1))))),0),0),LEN(A1)) In the above example, B1 returns: ahjKJ yuUIOI Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Dan S" wrote in message ... I have a bunch of cell with varying lench that have numbers at the beginning (also of varying length. Ex: 1258 ahjKJ yuUIOI 45687045HJKoiuwER I just want to extract the text from these cell. How do I do that? Thanks, Dan |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripping nubers from text in a cell
Certainly better than what I threw together.
(I don't know WHAT I was thinking.) LOL... trust me, I know the feeling. As a matter-of-fact, I just did the same type of thing down a few threads in identifying the first Monday in a month. Fortunately for the OP, Ron Rosenfeld posted a saner formula to use. Rick |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripping nubers from text in a cell
I wasn't sure what to do about that. The OP said he wanted to extract the
text (non-numbers) and that leading space is text, so I left it in. Rick "T. Valko" wrote in message ... Needs to trim the space in the first example. -- Biff Microsoft Excel MVP "Ron Coderre" wrote in message ... Certainly better than what I threw together. (I don't know WHAT I was thinking.) Ron "Rick Rothstein (MVP - VB)" wrote in message ... Assuming your text is not longer than 999 characters and that your digits are all next to each other (no leading or intervening spaces or characters)... =MID(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))+1,999) Rick "Dan S" wrote in message ... I have a bunch of cell with varying lench that have numbers at the beginning (also of varying length. Ex: 1258 ahjKJ yuUIOI 45687045HJKoiuwER I just want to extract the text from these cell. How do I do that? Thanks, Dan |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stripping nubers from text in a cell
Well, I'm just glad I could help.
-------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Dan S" wrote in message ... I think that might do the trick...I wish there was something a little simplier (built in the Excel) but I appreciate the help! Dan "Ron Coderre" wrote: With A1: (containing text that may begin with numbers.....eg 1258 ahjKJ yuUIOI) This formula (in sections to compensate for text wrap) returns the text to the right of those beginning numbers: B1: =MID(A1,MATCH(FALSE,INDEX(ISNUMBER(--LEFT(A1,ROW(INDEX($A:$A,1): INDEX($A:$A,LEN(A1))))),0),0),LEN(A1)) In the above example, B1 returns: ahjKJ yuUIOI Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Dan S" wrote in message ... I have a bunch of cell with varying lench that have numbers at the beginning (also of varying length. Ex: 1258 ahjKJ yuUIOI 45687045HJKoiuwER I just want to extract the text from these cell. How do I do that? Thanks, Dan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stripping text from numbers in a cell | Excel Worksheet Functions | |||
| Stripping text before a number (alpha or numeric) | Excel Worksheet Functions | |||
| Add numbers accross columns after stripping away text | Excel Discussion (Misc queries) | |||
| Stripping section of text | Excel Worksheet Functions | |||
| Stripping mixed number and text | Excel Worksheet Functions | |||
Linear Mode
