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!
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!
Last edited by TiberSeptim; 05-20-2020 at 03:50 AM.
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.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
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.
Happy with my advice? Click on the * reputation button below
thanks for the answers!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.
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!
Last edited by TiberSeptim; 05-19-2020 at 12:21 AM.
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.
This macro looks for your existing data in Column A starting at Row 1 and outputs the split text starting in Column B. Also, this code will handle as many lower/upper case combinations as there are in the cells (it is not limited to just one such combination of characters).
Please Login or Register to view this content.
Thank you very much!
Try this...
Please Login or Register to view this content.
Thank you!
You must be musical fan
Try:
**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).Please Login or Register to view this content.
Quang PT
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
Last edited by XOR LX; 05-20-2020 at 02:34 AM.
You are amazing bebo!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks