Lakshman ( & Deba ) - Rajan posted an excellent formula for extracting all
the numbers already.
Make sure to enter his formula with ctrl-shift-enter (it's an array
formula). It is for Excel 2007+ only because it uses the IFERROR function.
Here is a version modified slightly to work in older Excel versions too:
=IF(ISERROR(MATCH(TRUE,ISNUMBER(INT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),
0)),"",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(MID(
A1,ROW(INDIRECT("1:"&LEN(A1))),1))))))),1)*10^ROW(INDIRECT("1:"&SUM(--ISNUMB
ER(INT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))))))/10)
Use Ctrl-Shift-Enter to confirm entry.
A formula for extracting text only is more problematic. To my knowledge, it
is not possible to extract all text and return a single value using a
formula alone, but a formula could extract the first piece of text, or an
array containing all of the text values.
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of LAKSHMAN PRASAD
Sent: Monday, May 28, 2012 11:49 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ How to collect only numeric value from a
particular cell
Dear Noorain,
Your macro is working, but if there is any formula for this.?
And one thing more i think under formula is not working properly which was
post by buddy(MR. RAJAN)
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(MID
(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))))),1)*10^ROW(INDIRECT("1:"&SUM(--ISNUM
BER(INT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))))))/10,"")
Thanks Noorain
LAKSHMAN
From: NOORAIN ANSARI <noorain.ans...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Tuesday, May 29, 2012 11:57 AM
Subject: Re: $$Excel-Macros$$ How to collect only numeric value from a
particular cell
Dear Lakshman,Please see attached sheet, It is working fine..Let's know
where are you facing problem.-- Thanks & regards,
Noorain Ansari
www.noorainansari.com <http://www.noorainansari.com/>
<http://www.excelmacroworld.blogspot.com/> www.excelmacroworld.blogspot.com
On Tue, May 29, 2012 at 11:44 AM, LAKSHMAN PRASAD <lakshman_...@yahoo.com>
wrote:
Hello champ.,
This formula is picking up only first disit of cell only.
1001GANG
1
1002COL.
1
1002TEJI
1
1003SUDH
1
Regards
LAKSHMAN
From: Rajan_Verma <rajanverma1...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Monday, May 28, 2012 7:12 PM
Subject: RE: $$Excel-Macros$$ How to collect only numeric value from a
particular cell
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 [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 StringDim 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
<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
--
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
