Insert a carriage return

[tv_kid]

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.

[comment]

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.

[tv_kid]

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...

[comment]

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.