Extracting text from Address field

building reports webintelligence
1

Hi All,

I have an address field with a comma as a delimiter eg
123 West St, Westfield, Some Town, Post Code

and I want to extract each item into a cell by itself.

I have used

and this extracts 123 High St but I would be grateful for some help in extracting the other items

Many thanks

Seamus

sosullivan :uk: (BOB member since 2004-02-24)

2

This should be done through some ETL process, otherwise you can continue in the same way as you did for first part.

Prashant Purohit :india: (BOB member since 2009-02-18)

3

Prashant,

Thanks for the reply, but I still need some help in extracting the other items please

Thanks

Seamus

sosullivan :uk: (BOB member since 2004-02-24)

4

Fine if you want to do it in webI then I have a trick, you can either nest everything in a single formula or create intermediate variables.

You already extracted the first part, now use substring function to get the rest of the string. Again put same formula what you used for first part on the rest of the string, in that way you will get the second part. Now again use substring to get the remaining string (number returned by pos function will help you in doing that) and so on, your search will stop when there is no “,” present in your string.

Prashant Purohit :india: (BOB member since 2009-02-18)