Parse A String into Two

[xlmate]

I can't remember how do I separate the text into two
by finding the 2nd upeer case.

eg SmithJohn to Smith John

--

Appreciate your help.


Thank You

cheers, francis

 

[Glenn]

I can't remember how do I separate the text into two
by finding the 2nd upeer case.

eg SmithJohn to Smith John

Array formula (commit with CTRL+SHIFT+ENTER):

=LEFT(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0))&" "&
MID(A1,MATCH(0,--EXACT(MID(A1,ROW(2:99),1),
MID(LOWER(A1),ROW(2:99),1)),0)+1,LEN(A1))

 

[Ashish Mathur]

Hi,

You can try this array formula (Ctrl-Shift+Enter)

=MID(A10,MATCH(TRUE,EXACT(MID(A10,ROW($1:$28),1),PROPER(MID(A10,ROW($1:$28),1))),0),MATCH(TRUE,EXACT(MID(A10,ROW($2:$28),1),PROPER(MID(A10,ROW($2:$28),1))),0))&"
"&MID(A10,MATCH(TRUE,EXACT(MID(A10,ROW($2:$28),1),PROPER(MID(A10,ROW($2:$28),1))),0)+1,255)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ron Rosenfeld]

I can't remember how do I separate the text into two
by finding the 2nd upeer case.

eg SmithJohn to Smith John

You could download and install Longre's free morefunc.xll add-in (do a Google
search for morefunc.xll) and then use this formula:

=REGEX.SUBSTITUTE(A22,"([a-z])([A-Z])","[1] [2]")
--ron

 

[Teethless mama]

=REPLACE(A1,MAX(IF(ISERROR(FIND(CHAR(ROW(INDIRECT("65:90"))),A1)),"",FIND(CHAR(ROW(INDIRECT("65:90"))),A1))),0," ")

ctrl+shift+enter, not just enter

 

[xlmate]

Hi

Thanks. What does 65:90 in the Indirect function do?


--

Appreciate your help.


Thank You

cheers, francis

 

[xlmate]

Thanks, but the formula return S Bob instead of Smith Bob

--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you.


Thank You

cheers, francis

 

[xlmate]

Hi Glenn

My bad, I have missed a part of the formula. Your formula does return
the correct result.

Appreciate your help


Thank You

cheers, francis

 

[Glenn]

Hi

Thanks. What does 65:90 in the Indirect function do?

It's actually part of the CHAR() function. Refers to all of the capital letters
of the alphabet.

 

[xlmate]

Thanks for the guide

Appreciate your help

Thank You

cheers, francis

 

[xlmate]

Thank you for your effort and solution
Appreciate it


Thank You

cheers, francis

 

[Ashish Mathur]

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com