Try this:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUT E(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,""),2,""),3,""),4,""),5,""),6,""),7, ""),8,""),9,""),0,"") Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Deba Ranjan Sent: 29 May 2012 10:36 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ How to collect only numeric value from a particular cell Dear experts, its a awesome expertise work. Now - Can we extract only words and numbers separately from Alphanumeric, alphanumericalpha, Numericalpha, or numericalphanumeric. eg. MOHANAN157896 RAJU78785545GAYATRI 787984544RAJANANA 879445JOCKER7897777 thanks............... Thanks & Regards, Deba Ranjan P On Mon, May 28, 2012 at 7:12 PM, Rajan_Verma <rajanverma1...@gmail.com> wrote: Hi, If your data in Alphanumeric value in A1 then use it in B1 =IFERROR(SUM(INT(MID(A1,LARGE(--ISNUMBER(INT(MID(A1,ROW(INDIRECT("1:"&LEN(A1 ))),1)))*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&SUM(--ISNUMBER(INT(MI D(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))))),1)*10^ROW(INDIRECT("1:"&SUM(--ISNU MBER(INT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))))))/10,"") Regards Rajan verma +91 7838100659 <tel:%2B91%207838100659> [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of vishal angre Sent: 28 May 2012 4:52 To: excel-macros@googlegroups.com Cc: noorain.ans...@gmail.com Subject: Re: $$Excel-Macros$$ How to collect only numeric value from a particular cell Function removetext(c As String) Dim String1 As String Dim i As Integer String1 = "" For i = 1 To Len(c) If IsNumeric(Mid(c, i, 1)) Then String1 = String1 & Mid(c, i, 1) End If Next removetext = String1 End Function On Mon, May 28, 2012 at 4:30 PM, LAKSHMAN PRASAD <lakshman_...@yahoo.com> wrote: How to collect only numeric value from a particular cell 1462NIRM 1462 1464NIRM 1464 1465NIRM 1465 1404--BL 1404 1031Infr 1031 8020LOKN 8020 202D.B. 202 922Parmi 922 5071RIFA 5071 1040Harj 1040 5078Harj 5078 1471Raj 1471 1571Raj 1571 414Ruchi 414 2084AJAY 2084 Regards LAKSHMAN -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com <mailto:excel-macros%2bunsubscr...@googlegroups.com> -- With Regards, Vishal K. Angre E-mail : visha...@gmail.com vishal.k.an...@gmail.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com <mailto:excel-macros%2bunsubscr...@googlegroups.com> -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
