I'm having trouble figuring this one out and I wonder if some cleverer brains than mine can help...

I've imported a load of Addresses from Outlook, and many of them have two lines combined into one, e.g "1 The StreetThe Town" etc.

I can't figure out a way to seperate the data into two lines. It appears that there is always a lower case letter followed by an upper case letter, but I can't find a function to check for the 'case' of text.

Am I missing something obvious?

Many thanks in advance.

Are you sure there isn't an invisible character separating the rows?

Anyway, to test the case, you can use something like:

Exact ( Lower ( character ) ; character )

Of course, this only works when the character has both lower and upper case variants, otherwise it will be true always.

There may be an invisible character, but it appears the same in Outlook. How would I tell if there is an invisible character?

I used the following calculation to locate any 'problematic' records:

Exact( Proper (LeftValues (Address; 1) );  LeftValues (Address; 1))

It seemed to work, now I just have to find a way to insert the correct carriage return in there...

How would I tell if there is an invisible character?

Open the exported file in a text editor such as TextWrangler, and turn 'Show Invisibles' on.

now I just have to find a way to insert the correct carriage return

You coud probably do something like this:

1. Substitute all lower-case characters, numeric characters and punctuation (but not spaces or carriage returns) with "a", and all upper-case characters with "B";

2. Find the position of "aB" in the result;

3. If found, use the Replace() function) to insert carriage return at position + 1.

Or you could write a custom function/script to loop over the text examining a pair of characters at a time.