Extracting string of 10 Numbers from cell

[de_keda]

I have a column of thousands of cells that contain responses from a survey. Each cell contains an account number (or should) of exactly 10 numbers, but at different positions in the cell. The cell can also contain other numbers, but I need to extract just the 10-digit account number from each cell to another cell.

Example(1):
aa9876 reviewed 1234567891 and proceeded to credit $10

Example(2):
2345678912 completed

Example(3):
$50 credit. 3456789123 done. 3ab456 approved

Because this is a survey-type response, the user can enter anything they wish and submit it. But each response *should* contain the 10-digit account number.

I found and slightly modified the following CSE array formula (though I don't really understand it), but it only returns the first 10 numbers in the cell; as the account number is not always the first thing entered, it does not help:
=LEFT(SUM(MID(0&C31,LARGE(ISNUMBER(--MID(C31,ROW(INDIRECT("1:"&LEN(C31))),1))*ROW(INDIRECT("1:"&LEN(C31))),ROW(INDIRECT("1:"&LEN(C31))))+1,1)*10^ROW(INDIRECT("1:"&LEN(C31)))/10),10)

I need a formula that will search for the string of the 10-digit account number and return only that number, regardless of what other text/numbers are in the cell.

Thanks in advance!!!

 

[de_keda]

If you need a formula solution, please check this one:

=MID(A1,FIND(" "&REPT("|",10)&" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&A1&" ","0","|"),"1","|"),"2","|"),"3","|"),"4","|"),"5","|"),"6","|"),"7","|"),"8","|"),"9","|"),REPT("|",10)&",",REPT("|",10)&" "),REPT("|",10)&".",REPT("|",10)&" "),REPT("|",10)&":",REPT("|",10)&" "),REPT("|",10)&";",REPT("|",10)&" ")),10)

A formula solution would be the best in my case, but the formula here isn't quite working. Keep getting a "#VALUE!" and I can't figure out why.......

 

[Gerald Higgins]

Try using the Formula Evaluation tool to find out which bit it's failing on.

 

[Rick Rothstein]

I tried pasting the code in a Module...

The code Tetra201 posted does not go in a regular module... it is event code which must be placed in the worksheet's code module. From previous postings of mine...

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[de_keda]

The code Tetra201 posted does not go in a regular module... it is event code which must be placed in the worksheet's code module. From previous postings of mine...

Thanks Rick. I think I got it working now. I appreciate all the feedback!!!

 

[de_keda]

Try using the Formula Evaluation tool to find out which bit it's failing on.

I used the Evaluate Formula tool and it says "The next evaluation will result in an error." and shows: "=MID(0,FIND(" |||||||||| "," "),10)"

If I'm understanding it correctly, all the substitute imbedded formulas are working, but it's having an issue with the find formula. Not sure if it's looking for ten of the "|" symbols though, or if the substitute formulas are correctly stated?

 

[István Hirsch]

A formula solution would be the best in my case, but the formula here isn't quite working. Keep getting a "#VALUE!" and I can't figure out why.......

At me my formula works, results can be seen in column B, for comparison results produced by the code in post #12 can be seen in column C. They are similar, except that the latter considers parts of a decimal number as account numbers.

Do you work with comma as argument separator?

*	A	B	C
1	aa9876 123 4567891 reviewed 1234567891 and to credit $10	1234567891	1234567891
2	aa9876 1234567891 reviewed 12345678212 and to credit $10	1234567891	1234567891
3	aa9876 123456789 reviewed 12-11-2016 *and to credit $10	#VALUE!	*
4	aa9876 0004567891 reviewed 1-1-2016 *and to credit $10	0004567891	0004567891
5	wr wre 1.2345678910 werwer	#VALUE!	1 2345678910
6	123456780	#VALUE!	*
7	aaa 1234567890.23	#VALUE!	1234567890 23
8	aa9876 reviewed 12345678e1 and 123456789. to credit $10	#VALUE!	*
9	aa9876 reviewed 12345678e1 and 1234567891 to credit $10	1234567891	1234567891
10	aa9876 reviewed 11-12-2016 and 1234567891 to credit $10	1234567891	1234567891
11	aa9876 reviewed 12345678e1 and 1234567891 to credit $10	1234567891	1234567891
12	Bad number 1234567890123 followed by 9876543210 which is the desired	9876543210	1234567890123
13	Bad number abc1234567890def followed by 9876543210 the good number	9876543210	1234567890
14	qwwe 12.3456789765	#VALUE!	12 3456789765
15	12-11-2016 erer	#VALUE!	*
16	1234567890 erte blabla 231456789	1234567890	1234567890
17	12-11-20160 erer	#VALUE!	*
18	02/12/202	#VALUE!	*
19	02/12/203	#VALUE!	*
20	12-11-20160 erer	#VALUE!	*

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =MID(A1,FIND(" "&REPT("|",10)&" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&A1&" ","0","|"),"1","|"),"2","|"),"3","|"),"4","|"),"5","|"),"6","|"),"7","|"),"8","|"),"9","|"),","," "),":"," "),";"," "),REPT("|",10)&". ",REPT("|",10)&" ")),10) B2 =MID(A2,FIND(" "&REPT("|",10)&" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&A2&" ","0","|"),"1","|"),"2","|"),"3","|"),"4","|"),"5","|"),"6","|"),"7","|"),"8","|"),"9","|"),","," "),":"," "),";"," "),REPT("|",10)&". ",REPT("|",10)&" ")),10) B3 =MID(A3,FIND(" "&REPT("|",10)&" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&A3&" ","0","|"),"1","|"),"2","|"),"3","|"),"4","|"),"5","|"),"6","|"),"7","|"),"8","|"),"9","|"),","," "),":"," "),";"," "),REPT("|",10)&". ",REPT("|",10)&" ")),10) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[István Hirsch]

continued..

I see now there are other differences.


In row 12 the code extracted a 13-digit number instead of the 10-digit number.
In row 13 it extracts a number which is associated with letters, but does not extract the 10-digit number separated by spaces.