Parse A String into Two

  • Thread starter Thread starter xlmate
  • Start date Start date
X

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
 
xlmate said:
I can't remember how do I separate the text into two
by finding the 2nd upeer case.

eg SmithJohn to Smith John
Click to expand...


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))
 
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
 
I can't remember how do I separate the text into two
by finding the 2nd upeer case.

eg SmithJohn to Smith John
Click to expand...

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
 
=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
 
Hi

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


--

Appreciate your help.


Thank You

cheers, francis
 
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
 
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
 
xlmate said:
Hi

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

It's actually part of the CHAR() function. Refers to all of the capital letters
of the alphabet.
 
Thank you for your effort and solution
Appreciate it


Thank You

cheers, francis
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Parsing a text string into separate cells 1
Excel Comparing two columns 1
parsing a full name out into Last, First, Middle 2
parsing name in cell by comma? 2
Swapping two words in one cell 1
Swapping two word and placing them in another cell 1
Parse cell contents to new columns 6
Adding Text from seperate columns 2

Back
Top