| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| parse text string | Excel Worksheet Functions | |||
| Parse this string | Excel Discussion (Misc queries) | |||
| How to parse a string with a date? | Excel Worksheet Functions | |||
| Parse data where break is a first uppercase character in a string? | Excel Worksheet Functions | |||
| Q: parse string | Excel Discussion (Misc queries) | |||
Linear Mode
