 |
 | 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 / New Users / March 2007Need formula to Identify Cells that contain both Numbers and Text
Hi, I have a spreadsheet that has some cells in a column containing both text and numbers, i.e. a phone number, city and state. In that same column I have cells that only contain a city and state. I need to come up with a formula that will identify which cells do not contain numbers in them. Please advise, Steve Steve If the number is always first then in a column near it (We'll say the number/text is in column A enter) =IF(ISNUMBER(VALUE(LEFT(A1,1))),"Number","Text") Some may be overkill but I add VALUE to make sure it tries to parse the first character to a number  SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk > Hi, > [quoted text clipped - 7 lines] > > Steve And if the number is *not* the first character in the cell, you might try this: =IF(ISERROR(LOOKUP(99^99,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))),"NO Num","Number") SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Steve If the number is always first then in a column near it (We'll say the number/text is in column A enter) =IF(ISNUMBER(VALUE(LEFT(A1,1))),"Number","Text") Some may be overkill but I add VALUE to make sure it tries to parse the first character to a number SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk > Hi, > [quoted text clipped - 7 lines] > > Steve Nick - This last formula took care of things perfectly....Thanks, Steve > And if the number is *not* the first character in the cell, you might try > this: [quoted text clipped - 44 lines] > > - Show quoted text - Well ... you almost got it right ... the name is Rick. But ... how would you know that?<bg> Anyway, appreciate the feed-back. SignatureRegards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- Nick - This last formula took care of things perfectly....Thanks, Steve On Mar 22, 9:02 pm, "RagDyeR" <ragd...@cutoutmsn.com> wrote: > And if the number is *not* the first character in the cell, you might try > this: [quoted text clipped - 47 lines] > > - Show quoted text - |
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.