My inclination would be to use the substitute function to substitute pilcrows(hard returns) wherever commas are found. Provided there's only one comma, this will work nicely, allowing you to then Getvalue(substitutedtext ;1) for the city
I'm not at a computer right now, and can't try this, but what about Right(address field; -7) or -8 if the space must be counted? I have no idea if this would work.
Thank you for your help. Matthew, the substitute function along with the Getvalue work like a charm.
Rick, I also tried the Right text function with -10 but could not get it to work. I don't understand why…it seems like a logical answer.
Thanks again for your help…you saved me a lot of time.
" I also tried the Right text function with -10 but could not get it to work."
Because you should use the Left( ) function, something like:
Left ( YourText ; Length ( YourText ) - 10 )
Raybundi's solution also works using the Left text function. Unfortunately I can only mark one answer as correct.
Thanks for helping work out the solution using just the Text function without the Substitute. They both do the job
This is notmy solution!
It 's justthe explanation ofwhy,usingthe Right () function, it did not work.I would haveused the samesolution proposed byMatthew.