Extract numerics from a cell ?

[theta]

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?

 

[Sandeep Warrier]

How about

Excel Workbook
	A	B	C	D
1	ABC01234567/01	1234567	123456	1234567010
Sheet1

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Sandeep Warrier]

If 7 and / always occur next to each other then for the 2nd formula

Excel Workbook
	A	B	C	D
1	ABC01234567/01	1234567	123456	1234567010
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C1	=LEFT(B1,LEN(B1)-1)+0

 

[Jon von der Heyden]

1st formula courtesy of Ron Coderre:

Excel Workbook
	A	B	C	D
1	ABC01234567/01		123456701	< All Nums
2			1234567	< before /
3			123456	< before 7/
Sheet1

Excel 2007

Cell Formulas
Range	Formula
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]

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 ?

 

[theta]

Also

http://www.excelforum.com/excel-general/707687-summing-numeric-parts-of-alphanumeric-strings.html

 

[vinod9111]

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]

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
	A	B	C	D
1		All_Numbers	Before *-/	Before 7/
2	ABC01234567/01	123456701	1234567	123456
3	fsd
4	54634	54634
5	XYZ89766/99	8976699	89766
6	CBGH1235KJ7/124MM	12357124	12357	1235
Extract cell characters

 

[theta]

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