ExcelBanter - Parse A String into Two

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Parse A String into Two (https://www.excelbanter.com/excel-worksheet-functions/221631-parse-string-into-two.html)

xlmate

Parse A String into Two

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

Parse A String into Two

xlmate wrote:
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[_2_]

Parse A String into Two

Hi,

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

=MID(A10,MATCH(TRUE,EXACT(MID(A10,ROW($1:$28),1),P ROPER(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

"xlmate" wrote in message
...
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

Ron Rosenfeld

Parse A String into Two

On Thu, 19 Feb 2009 10:23:02 -0800, xlmate wrote:

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

Parse A String into Two

=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" wrote:

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

xlmate

Parse A String into Two

Hi

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

--

Appreciate your help.

Thank You

cheers, francis

"Teethless mama" wrote:

=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" wrote:

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

xlmate

Parse A String into Two

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

"Glenn" wrote:

xlmate wrote:
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))

xlmate

Parse A String into Two

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" wrote:

xlmate wrote:
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))

Glenn

Parse A String into Two

xlmate wrote:
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

Parse A String into Two

Thanks for the guide

Appreciate your help

Thank You

cheers, francis

"Glenn" wrote:

xlmate wrote:
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

Parse A String into Two

Thank you for your effort and solution
Appreciate it

Thank You

cheers, francis

"Ashish Mathur" wrote:

Hi,

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

=MID(A10,MATCH(TRUE,EXACT(MID(A10,ROW($1:$28),1),P ROPER(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

"xlmate" wrote in message
...
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

Ashish Mathur[_2_]

Parse A String into Two

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"xlmate" wrote in message
...
Thank you for your effort and solution
Appreciate it

Thank You

cheers, francis

"Ashish Mathur" wrote:

Hi,

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

=MID(A10,MATCH(TRUE,EXACT(MID(A10,ROW($1:$28),1),P ROPER(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

"xlmate" wrote in message
...
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

All times are GMT +1. The time now is 09:22 AM.