Formula to copy only numbers from a text/number column

[rockvibe]

Text
A7b12 890c(d.e 123456
A7b12 890c(d.e:123456
A7b12 890c(d.e 12345
A7b12 890c(d.e:12345

123456 a7b12 890c(d.e
123456:a7b12 890c(d.e
12345 a7b12 890c(d.e
12345:a7b12 890c(d.e

A7b12 890c(d.e 123456 a7b12 890c(d.e
A7b12 890c(d.e:123456:a7b12 890c(d.e
A7b12 890c(d.e 12345 a7b12 890c(d.e
A7b12 890c(d.e:12345:a7b12 890c(d.e

12345
12345:
123456
A7b12890c(d.e:12345:a7b12890c(d.e
A7b12890c(d.e:123456:a7b12890c(d.e

I need a formula that can unify all of the above cases and copy a random 5- or 6-digit number from the Text column to another Number column. The catch is that these numbers might be in different positions and be preceeded or continued not only by letters and symbols but by other numbers of 1-,2-,3-,4-,7- and 8-digit numbers. However it is not a 100% defined where the numbers are positioned and if they are preceeded ot continued at all. The 5- and 6- digit numbers that are to be copied are 12345 and 123456 respectively.
* I need one formula because the list I need it for is around 30 000 entries long.

[bmxfreedom]

There needs to be something consistent to find the number's position within the string. The number itself can be that consistency, but I'm not sure by your post if that is the case. If it is the case, then the two numbers you've listed (12345, and 123456) can be pulled out of the strings with the following formula if they exist:

Please Login or Register  to view this content.

If your numbers are in cells, replaced the "123456" with the cell address. If the numbers definitely always exist, the formula can be shortened by removing some of the IF statements and just using

Please Login or Register  to view this content.

[rockvibe]

No, unfortunately it is not a static number. It is always different and might be in a different position, but it is always the 5- or 6- digit number in the row that needs to be copied.

example: "42asdasd 467305" where 467305 needs to be copied
or "asdoh423489 asfgdjnk" where 423489 needs to be copied
or "skg 96823klk 3231" where 96823 needs to be copied

[Ghozi Alkatiri]

=if(isnumber(find(123456,a1)),123456,if(isnumber(find(12345,a1)),12345))

[rockvibe]

As stated above this formula will not work because the number to be extracted is not always static but variable in each row. The only constant for the number is its 5- and 6-digit nature.

[ChemistB]

I think you'll need a UDF. You might want to ask the moderators to move this to the programming folder.

[bmxfreedom]

You mentioned that the number of characters in each number string is always 5 or 6, but there are sometimes 8 character number strings preceding or following them. Did you mean following directly, or following after a space or other text? i.e., if the number your looking for is 467305 is included in a string that looks like 42asdasd 467305 12345678, my original formula will still work so long as you can define the number being found in this case 467305.
If the 5 or 6 digit number is a total unknown (other than the fact that it exists within the string), you may need to apply nested if statements that search for any 5 or 6 digit number within the string by using iserror(mid(string,1,6)*1) and iserror(mid(string,1,5)*1), then returning the first result when this is false. This would only work if the potential 8 digit numbers you speak of are always after the value you are searching for.

[bmxfreedom]

It might be even easier to understand, or to write a formula, if you split the string up by groups of 6 then 5 for each character that exists. This would require a lengthy series of formulas though...

[AlKey]

I also think that to get the results you looking for can only be done by using UDF (User Defined Function)

I think this code will work for you. It 12345 and 12346

To open VB editor press and hold ALT then press F11. Click Insert and select Module. Paste code into the module and close VB editor.

No use formula =NumberOut(A1) and drag it down

Please Login or Register  to view this content.

[bmxfreedom]

Here's a macro that will loop through the possible position based search formulas for each cell in column a, and return the first resulting formula that results in a 6 or 5 digit number string in column B. It takes 32 seconds on my system to write unique formulas for each of the 30,000 rows of your text duplicated to fill the column.

Please Login or Register  to view this content.

I've attached a copy of the workbook so you can try it out.

[bmxfreedom]

Range("D2").Value = Now()

That part of the code can be removed, along with the associated cells in column D. They're just there as a time measuring method.

Also, "Sheet1" would need to be changed to whatever the actual sheet is named, and all instances of "B" & curRow or "B" & r would need to be changed to reflect the column you wish to have the formulas present in; say column C instead of B, then it would be "C" & curRow and "C" & r.

[bmxfreedom]

The problem with the UDF method is that it is very processor intensive. With 30,000 rows of data, it will take about 15 seconds to recalculate your workbook anytime you need to. The formula creation method takes longer at first, but after the formulas are written, they are much faster to calculate. (under a second for all 30,000). The formula creation method can also be limited to a single cell at a time if you want (similar to the UDF) by removing the double loop and just using:

Please Login or Register  to view this content.

[bmxfreedom]

I just noticed that the UDF function here doesn't return the value 12890 in the string "A7b12890c(d.e:123456:a7b12890c(d.e ", where my formula creation method does. This brings the question, which 5 or 6 character number do you want out of this string? If you change the 123456 in this string to something that is not 5 or 6 digits, the UDF fails to pick up the other 5 digit number; 12890...
AlKey, I'm hoping to understand this UDF better. How does it determine that the middle 5 or 6 digit number set in this scenario is the only one to return, while in other strings, it returns the same characters if they appear at the beginning or the end of the string?
RockVibe, can you shed some light on this question?
AlKey, this is a fascinating function. I would love to know more about it if you have the time.
Thanks