 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008Stripping 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 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 >I have a bunch of cell with varying lench that have numbers at the >beginning [quoted text clipped - 7 lines] > Thanks, > Dan Certainly better than what I threw together. (I don't know WHAT I was thinking.) Ron > 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 [quoted text clipped - 15 lines] >> Thanks, >> Dan Needs to trim the space in the first example.  SignatureBiff Microsoft Excel MVP > Certainly better than what I threw together. > (I don't know WHAT I was thinking.) [quoted text clipped - 20 lines] >>> Thanks, >>> Dan 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 > Needs to trim the space in the first example. > [quoted text clipped - 22 lines] >>>> Thanks, >>>> Dan > 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 On Thu, 6 Mar 2008 18:01:32 -0500, "Rick Rothstein \(MVP - VB\)" <rick.newsNO.SPAM@NO.SPAMverizon.net> 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 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) >I have a bunch of cell with varying lench that have numbers at the >beginning [quoted text clipped - 7 lines] > Thanks, > Dan 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) > With > [quoted text clipped - 27 lines] >> Thanks, >> Dan 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 > With > [quoted text clipped - 27 lines] > > Thanks, > > Dan Well, I'm just glad I could help. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) >I think that might do the trick...I wish there was something a little > simplier (built in the Excel) but I appreciate the help! [quoted text clipped - 33 lines] >> > Thanks, >> > Dan *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,255)) SignatureBiff Microsoft Excel MVP >I have a bunch of cell with varying lench that have numbers at the >beginning [quoted text clipped - 7 lines] > Thanks, > Dan |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.