Formula to determine a sequence of 6 numbers in a text

[drajanm]

Hi,

I have a column (say B) with addresses entered in the cells. Within these addresses there exists a postal code of 6 digits. (Something like 52 North Street, 234056 -NZ).

I want to capture this string of 6 digits in a separate column (say C).

The problem is that, the sequence of 6 digits can be present anywhere in the address, not necessarily at the end. And the addresses themselves are of varying lengths, so the conventional RIGHT()/MID() functions wont be applicable here.

There exists only one such continuous string of 6 digits in the entire address. Is it possible to use a formula to identify this?

[FDibbins]

will there always be a comma , before the numbers?

can you supply a few more examples?

[Moo the Dog]

Here's what I tried... not sure if it is something you can work with:

1. Copy values from column B (assume b2:b10 for this example) to column A of a blank sheet.
2. Run a 'Text-to-Columns' for the copied column, using a SPACE as the delimiter. This should spread the address across 5 or 6 cells.
3. If the first row is A2, then in say, J2, put this formula: =INDEX(A2:I2,1,MATCH(99^99,A2:I2,1)) and fill down

That should give you the postal codes.

- Moo

[Moo the Dog]

Actually, since you said the Postal Code can be anywhere in the cell, my previous formula would only return the correct result if the Postal Code is the last numeric value in the row after performing the Text-to-Columns. If the address number comes after the Postal Code, it would return the address number.

Try this formula instead: =MAXA(A2:I2)

or, if that doesn't work:

An ARRAY formula (entered with Ctrl + Shift + Enter) then filled down:
=INDEX(MAX(IF(ISNUMBER(A2:I2),A2:I2)),1)

- Moo