Triming text

[katmando]

I've got an address in one field which i want to seperate by store, address, city and postcode. The problem I have is that the bits I want to seperate out have vairable lenghts so using a left or mid function with a set length does not work

Sample of field
(SOLIHULL) 17 MILL LANE ARCADE TOUCHWOOD e146

 

[namliam]

What would be your store, address, city and postcode in your example.

Is your example as it allways is? So eg. store is allways between ()

Seasons greetings from Amsterdam

The Mailman

 

[katmando]

store address city postcode
(SOLIHULL) 17 MILL LANE ARCADE TOUCHWOOD e146

at present there would be a bracket round the store

Cheers from london

 

[namliam]

Storename: left([Yourfield], instr(1,[yourfield],")")+1)
In this case: (SOLIHULL)

Postcode: mid([yourfield], instrrev([yourfield], " ",-1) )
In this case e146

city you can use instrrev as well... Try it.. But that is assuming you do not need city's with spaces in em, cause that is going to be more tricky.

Then finaly extract the address, where you should combine the storename and city thing together... in a mid function...

As you can see... doable?!

Good luck