Formula to extract Registration Number (ABN) from text in a cell

Hi,

try this formula:-=SUBSTITUTE(MID(B2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0,1,2,3,4,5,6,7,8,9")),LEN(B2))," ","")

let me know it works or not.

Thanks
Anil

Enter in B2 with Shift+Ctrl+Enter, not just Enter:

=MID(SUBSTITUTE(A2," ",""),MATCH(TRUE,ISNUMBER(--MID(SUBSTITUTE(A2," ",""),ROW($1:$130),11)),0),11)+0

Thanks for the suggestion - that works to get rid of spaces, but not the other details that I don't want. I might be able to use it as part of the answer but it's not enough on it's own.

Sorry I could not get this formula to work.

Originally Posted by István Hirsch

Enter in B2 with Shift+Ctrl+Enter, not just Enter:

=MID(SUBSTITUTE(A2," ",""),MATCH(TRUE,ISNUMBER(--MID(SUBSTITUTE(A2," ",""),ROW($1:$130),11)),0),11)+0

Thanks István - that works beautifully!

Originally Posted by Richard Buttrey

Hi,

So what is it with Itsvan's array formula (apart from referencing D2 rather than A2), that is not working?
It seems to give the right results for me. Remember it's an array formula entered with Ctrl-Shift-Enter

No that is me being a noob and not understanding how to reply propperly. It is Anil KMR's formula that I could not get to work :-/

If you are going to insert new rows between the existing ones later, use this formula:

=MID(SUBSTITUTE(A2," ",""),MATCH(TRUE,ISNUMBER(--MID(SUBSTITUTE(A2," ",""),ROW(INDIRECT("1:"&LEN(A2))),11)),0),11)+0

Originally Posted by István Hirsch

If you are going to insert new rows between the existing ones later, use this formula:

=MID(SUBSTITUTE(A2," ",""),MATCH(TRUE,ISNUMBER(--MID(SUBSTITUTE(A2," ",""),ROW(INDIRECT("1:"&LEN(A2))),11)),0),11)+0

Thanks again István, but when I tried the above formula I get an error message, and excel directs me to "0[" of the formula. I assume this also an array formula and requires activation with CSE.
I don't know much about array formulas (and totally no clue what the double minus sign does) and don't understand this one well enough to correct it myself.

Also I'm not sure why I need a different formula if I'm going to insert a row, I tried this to see what would happen and copied your original formula down from the above cell and had no apparent problems.

What is the advantage of the amended formula that you've posted?

Cheers.

Thanks for the feedback.

For me the formula in post #6 and post #12 gives the same result.

im1.jpg

im2 .jpg

Comparison of the two formulas:

While $1 increases to $130 in the part ROW($1:$130), the formula in post #6 checks if the string from the character positions 1-11, then 2-12, then 3-14 etc. up to 130-141 is a 11-digit long number or not, supposing that the whole length of the strings processed will not be longer than 130 characters.

If two new cells are inserted then the formula in B2 (and similarly B5 and B6) changes to:

MID(SUBSTITUTE(A2;" ";"");MATCH(TRUE;ISNUMBER(--MID(SUBSTITUTE(A2;" ";"");ROW($1:$132);11));0);11)+0

This means that the former checking will be done up to the 132nd character position in spite of the fact that the actual string-lengths are around 50 character. As a length of 130 character seems enough to cover the possible length of the strings to be processed, going over 130 is needless.
The second formula processes exactly as many characters as needed, that is, the length of the string, not more. Moreover it does not show the above-mentioned „shifting” effect.

Originally Posted by István Hirsch

Thanks for the feedback.

For me the formula in post #6 and post #12 gives the same result.

Attachment 370909

Attachment 370910

Comparison of the two formulas:

While $1 increases to $130 in the part ROW($1:$130), the formula in post #6 checks if the string from the character positions 1-11, then 2-12, then 3-14 etc. up to 130-141 is a 11-digit long number or not, supposing that the whole length of the strings processed will not be longer than 130 characters.

If two new cells are inserted then the formula in B2 (and similarly B5 and B6) changes to:

MID(SUBSTITUTE(A2;" ";"");MATCH(TRUE;ISNUMBER(--MID(SUBSTITUTE(A2;" ";"");ROW($1:$132);11));0);11)+0

This means that the former checking will be done up to the 132nd character position in spite of the fact that the actual string-lengths are around 50 character. As a length of 130 character seems enough to cover the possible length of the strings to be processed, going over 130 is needless.
The second formula processes exactly as many characters as needed, that is, the length of the string, not more. Moreover it does not show the above-mentioned „shifting” effect.

Hey thanks again, really appreciate teh help understanding the formula - I'd add more to your reputation, but umm excel forum wont let me, for now :-/

No problem, thanks for the feedback.