Check for lowercase & uppercase letter next to each other in a string

Is there anyway to identify where a lowercase is next to an uppercase in a string?
For example: CristianoRonaldo
i want to check for "oR" and separate this string into two words
Thanks!

If the name is in A2, put this in B2 to get the first name:

=LEFT(A2,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1)

Then put this in C2 to get the last name:

=RIGHT(A2, LEN(A2)-LEN(B2))


Copy down ad nauseum.

Hi and welcome

Providing you only have one capital letter other than the first in your string this will separate and add the space . it is an arry formula though so will need to be entered with control + shift + enter. if your surname has two Capitals then it won't work (ie McDonald, O'Rourke and the like)

Please Login or Register  to view this content.

If the name is in A2, put this in B2 to get the first name:

=LEFT(A2,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1)

Then put this in C2 to get the last name:

=RIGHT(A2, LEN(A2)-LEN(B2))


Copy down ad nauseum.

thanks for the answers!
However, it seems not to work on strings like this "Hanh Khuc Ngay Va DemPhan Huynh Dieu", only get "Hanh", not "Hanh Khuc Ngay Va Dem"
Please help
Thanks!

Not sure how good you are with Macros and VBA. This will put a space infront of each Capital letter in column A

Please Login or Register  to view this content.

You could separate them but we’d need to know the rule or logic to be used to know which parts go where.

You must be musical fan
Try:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Hi,

=LEFT(A1,MATCH(2,MMULT(N(ABS({109.5,77.5}-CODE(MID(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),2),{1,2},1)))<13),{1;1}),0))

No need for CSE with that one.

Regards

Originally Posted by XOR LX

Hi,

=LEFT(A1,MATCH(2,MMULT(N(ABS({109.5,77.5}-CODE(MID(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),2),{1,2},1)))<13),{1;1}),0))

No need for CSE with that one.

Regards

"Hanh Khuc Ngay Va DemPhan Huynh Dieu" becomes "Hanh Khuc Ngay Va Dem"
You missed 2nd part.
More info:
"Hanh Khuc Ngay Va Dem" is name of a Vietnamese famous song, and "Phan Huynh Dieu" is author, and the OP wishes to keep them separately.

Originally Posted by bebo021999

"Hanh Khuc Ngay Va DemPhan Huynh Dieu" becomes "Hanh Khuc Ngay Va Dem"
You missed 2nd part.
More info:
"Hanh Khuc Ngay Va Dem" is name of a Vietnamese famous song, and "Phan Huynh Dieu" is author, and the OP wishes to keep them separately.

I didn't miss the second part. The required formula for that part is evidently straightforward and has already been given, e.g. by JBeaucaire.

Originally Posted by JBeaucaire

Then put this in C2 to get the last name:

=RIGHT(A2, LEN(A2)-LEN(B2))

An alternative would be:

=SUBSTITUTE(A2,B2,"")

Regards