Extract varying lengths of text

Hi,

In I2,

=TRIM(SUBSTITUTE(SUBSTITUTE(H2,J2&" ",""),K2,""))

J2,

=TRIM(LEFT(RIGHT(H2,8),3))

K2,

=RIGHT(H2,4)

HTH

Hi Mitch,

The =FIND(" ",E30,1) formula will tell you the position of the spaces. You can use LEN, LEFT, MID and RIGHT formulas to extract info. There's more about the formulas in help. FIND will look from the left whereas it would be great for you if it looked from the end of the text.
Have you thought about "Data text to columns"? Try that then CONCATENATE the place names.
Another option is to download the postcode list from www.auspost.com.au and LOOKUP names for each postcode. Problem is, many postcodes have multiple place names but that's a start....

Good luck,
Bruce

I like Kris' idea better. It works...

G'day Kris and Bruce,

Unfortunately, Kris' formula won't work, because the number of spaces between words in column H varies. For example, consider the following - each line represents a cell (H8 and H9):
As you can see, there are two spaces between each set of characters in cell H8, but three between each in cell H9.

I probably need a UDF that counts the number of spaces, because it seems as though the number of spaces is uniform within each cell (i.e. if there are two spaces between the first and second word, there will be two spaces between the second and third words). The UDF would then need to use these spaces as delimeters, then split the whole words into sets (i.e. starting from the end, the first set will be the Post Code - 4 characters - then the next set, being ether 2 or three characters, will be the State - and the remaining words will be the Suburb).

But I don't know how to put this into code.

Thanks,

Mitch

Just an update:

Where there are two or more words making up the suburb name, then only one space exists between them; however, there are usually two spaces between the other words. While some states and post codes have three spaces between them, my prior statement of this being uniform within a cell still stands. IOW, if H8 has two spaces between the Suburb and State, there will be two spaces between the State and Post Code.

Any assistance would be appreciated,

Regards,

Mitch

Almost Solved

Here is the result I came up with. While it is a work-around, it works for all cells in the column.

First, in I2 I have this formula:

This makes sure that regardless of whether there are two or three spaces between each word, only the State and Post Code are extracted. There will be at least 10 characters and spaces between the last Suburb name and the last character in the cell (11 characters where there are three spaces between these words).

In J2 I have this formula:
Last of all, here is the formula for K2:
Thanks Kris and Bruce for contributing.

There is still one problem remaining - how to extract the suburb name(s)? Or how to remove the state and postcodes? I'll play around with the Substitute formula to see if that gives me any joy.

Regards,

Mitch

Hi,

Change J2 formula like this:

Either one.

1. =TRIM(LEFT(RIGHT(SUBSTITUTE(H2,CHAR(160),""),8),3))

2. =TRIM(LEFT(RIGHT(TRIM(H2),8),3))

I2:

Either one.

1. =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H2,CHAR(160),""),J2&" ",""),K2,""))

2. =TRIM(SUBSTITUTE(SUBSTITUTE(H2,J2&" ",""),K2,""))

HTH

Hi,

There is still one problem remaining - how to extract the suburb name(s)? Or how to remove the state and postcodes?

Click to expand...

Go to Data > Text to Columns.. check delimited > check Spaces > Next > Finish.

HTH

G'day Kris,

This works:
Excellent! Thanks Kris!

(Edit: Make post more readable)