Splitting Cells by Text Case
I came across a similar tip online and while I couldn’t recall ever having had a use for this, I thought it interesting nevertheless.
Suppose your worksheet has text values in the format “firstnameLASTNAME” and the user would like to separate the two names into two separate columns as determined by the case of the text.
The key is to determine where the text switches from lowercase to UPPERCUASE. To do this, you need to use an array formula.
Assuming your firstnameLASTNAME is in cell A1, the following array formula returns the string of text characters started by, and following, the first UPPERCASE letter in the text string:
Remember, since this is an array formula, you should enter it by pressing Ctrl+Shift+Enter.
It returns everything in the cell starting with the first uppercase letter it finds.
Thus, in “firstnameLASTNAME” it would return “LASTNAME” and in “firstnameLastname” it would return “Lastname”.
Assuming that you use the array formula in cell B1, you could then determine the first name by using the following:
=SUBSTITUTE(A1,B1,””) entered as a a regular formula, not an array formula.