Extract substring

[AlanKempster]

I have a table in which one field badly needs separating into two. Currently, it is laid out as a mixed case text string followed by an upper case text string, e.g. O little town of Bethlehem FOREST GREEN.

I need to move the upper case string (FOREST GREEN) to a new field (and then trim the original field accordingly). Since there is no delimiter between the two parts of the field, I have no idea how to proceed. Can anyone help?

Thanks.

 

[KeithG]

You need to make a procedure to step through each letter in the string and seperate the Capitalized and lowercase letters into two seperate fields.

 

[Brianwarnock]

To add to Keith's reply you would need to start from the right hand end of the string and step left to find the first lowercase letter, of course if the strings can contain other than letters then the problems are increased.

Functions such as Len, Mid will be used initially then Right and Left to do the separation.

Brian