Extracting string of 10 Numbers from cell

D

de_keda

New Member
Joined
May 22, 2015
Messages
8
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!!!
 
Sort by date Sort by votes
István Hirsch said:
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)
Click to expand...

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.......
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
de_keda said:
I tried pasting the code in a Module...
Click to expand...
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.
 
Upvote 0
Gerald Higgins said:
Try using the Formula Evaluation tool to find out which bit it's failing on.
Click to expand...

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?
 
Upvote 0
de_keda said:
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.......
Click to expand...

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?

*ABC
aa9876 123 4567891 reviewed 1234567891 and to credit $10
aa9876 1234567891 reviewed 12345678212 and to credit $10
aa9876 123456789 reviewed 12-11-2016 *and to credit $10#VALUE!*
aa9876 0004567891 reviewed 1-1-2016 *and to credit $10
wr wre 1.2345678910 werwer#VALUE!1 2345678910
#VALUE!*
aaa 1234567890.23#VALUE!1234567890 23
aa9876 reviewed 12345678e1 and 123456789. to credit $10#VALUE!*
aa9876 reviewed 12345678e1 and 1234567891 to credit $10
aa9876 reviewed 11-12-2016 and 1234567891 to credit $10
aa9876 reviewed 12345678e1 and 1234567891 to credit $10
Bad number 1234567890123 followed by 9876543210 which is the desired
Bad number abc1234567890def followed by 9876543210 the good number
qwwe 12.3456789765#VALUE!12 3456789765
12-11-2016 erer#VALUE!*
1234567890 erte blabla 231456789
12-11-20160 erer#VALUE!*
#VALUE!*
#VALUE!*
12-11-20160 erer#VALUE!*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]1234567891[/TD]
[TD="align: right"]1234567891[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1234567891[/TD]
[TD="align: right"]1234567891[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]0004567891[/TD]
[TD="align: right"]0004567891[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]123456780[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]1234567891[/TD]
[TD="align: right"]1234567891[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]1234567891[/TD]
[TD="align: right"]1234567891[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]1234567891[/TD]
[TD="align: right"]1234567891[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]9876543210[/TD]
[TD="align: right"]1234567890123[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]9876543210[/TD]
[TD="align: right"]1234567890[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"]1234567890[/TD]
[TD="align: right"]1234567890[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]02/12/202[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]02/12/203[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Extract 8 character number from text string
2
Replies
15
Views
614
Fluff
Fluff
C
  • Question Question
Find numbers that are 9 or 10 characters long in mixed string
Replies
6
Views
209
hagia_sofia
hagia_sofia
B
  • Solved
Extracting a specific number of characters from a multiline cell
Replies
7
Views
319
etaf
etaf
H
  • Solved
Extract 8 digit number from a string
2
Replies
14
Views
3K
Hans K
H
H
  • Question Question
Extract Data from Single Cell. Column is Constant. Number of Rows Involved Changes. Number of Rows Within a Single Cell Can be 1 Up to 9
Replies
6
Views
335
helpmeplz_
H

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top