Extract numerics from a cell ?

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960
Hi
Would like an array formula to strip numbers out of a string. I have seen one version using, help appreciated. I think there are some variations of =MID() where the contents of a cell are split into an array (1 character each) then analysed for digits.

ABC01234567/01

I would ideally like 2 formulas :

1. Split all numbers = 0123456701
2. Split before a given character = before *-/ = 01234567
3. Split before a pattern = before 7/ = 0123456

Any thoughts. Array with MID sounds like a good option?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
How about
Excel Workbook
ABCD
1ABC01234567/0112345671234561234567010
Sheet1
Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 
Last edited:

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
If 7 and / always occur next to each other then for the 2nd formula

Excel Workbook
ABCD
1ABC01234567/0112345671234561234567010
Sheet1
Excel 2003
Cell Formulas
RangeFormula
C1=LEFT(B1,LEN(B1)-1)+0
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,801
Office Version
365
Platform
Windows
1st formula courtesy of Ron Coderre:

Excel Workbook
ABCD
1ABC01234567/01123456701< All Nums
21234567< before /
3123456< before 7/
Sheet1
Excel 2007
Cell Formulas
RangeFormula
C1=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))*ROW(INDIRECT("1:" & LEN(A1))),0),ROW(INDIRECT("1:" & LEN(A1))))+1,1)*10^ROW(INDIRECT("1:" & LEN(A1)))/10)
C2=-LEFT(-C1,LEN(C1)-(LEN(A1)-FIND("/",A1)-1))
C3=-LEFT(-C1,LEN(C1)-(LEN(A1)-FIND("7/",A1)-1))
 

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960

ADVERTISEMENT

Hmmm....for the pattern match (7/) thought maybe could use COUNTIF array so that I may enter multiple patterns "on" "out" "7/" "hi"

Seen some usage like :

SUM(COUNTIF($B6:$AP6,{"x","d","m","e"})

http://office.microsoft.com/en-us/e...rs-from-alphanumeric-strings-HA001154901.aspx


Very elegant solutins, but not exactly what I need, yours look closer but want to use simple arrays rather than being too prescriptive ?
 

vinod9111

Active Member
Joined
Jan 21, 2009
Messages
425

ADVERTISEMENT

Another alternative which is not array posted by Bosco

FOR ALL NOS.

=-LOOKUP(2,-RIGHT(SUBSTITUTE(A1,"/",""),ROW($1:$99)))

and for remaining two requirement, you could use Jons formula for non array or Sandeep's formula for array.

regards,

Vinod
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,926
Office Version
365
Platform
Windows
I've tried some User-Defined Functions. Code pasted into a standard module and then formulas used in the sheet as shown and copied down.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> RX <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CreateRX()<br><SPAN style="color:#00007F">Set</SPAN> RX = CreateObject("VBScript.RegExp")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Function</SPAN> AllNumbers(<SPAN style="color:#00007F">ByVal</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)<br>    <SPAN style="color:#00007F">If</SPAN> RX <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> CreateRX<br>    <SPAN style="color:#00007F">With</SPAN> RX<br>        .Pattern = "[^0-9]"<br>        .Global = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Len(.Test(s)) <= Len(s) <SPAN style="color:#00007F">Then</SPAN><br>            AllNumbers = .Replace(s, "") + 0<br>        <SPAN style="color:#00007F">Else</SPAN><br>            AllNumbers = ""<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><SPAN style="color:#00007F">Function</SPAN> BeforeStr(<SPAN style="color:#00007F">ByVal</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, sStr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)<br>    <SPAN style="color:#00007F">If</SPAN> RX <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> CreateRX<br>    <SPAN style="color:#00007F">With</SPAN> RX<br>        .Pattern = sStr & ".*"<br>        .Global = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> .Test(s) <SPAN style="color:#00007F">Then</SPAN><br>            BeforeStr = AllNumbers(.Replace(s, "")) + 0<br>        <SPAN style="color:#00007F">Else</SPAN><br>            BeforeStr = ""<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>


Excel Workbook
ABCD
1All_NumbersBefore *-/Before 7/
2ABC01234567/011234567011234567123456
3fsd
45463454634
5XYZ89766/99897669989766
6CBGH1235KJ7/124MM12357124123571235
Extract cell characters
 

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960
Hi Jon....can you please breakdown your C1 formula for a full analysis so that I can develop it going forward. Looks like a more dynamic version of the array types i mentioend previously

Thanks

Luke
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,855
Messages
5,513,805
Members
408,971
Latest member
kay_dee8

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top