Parsing string - lower case followed by upper case...

[Matty]

Hi,

Anyone know of a formula that would parse the following:

MattyMatty

to:

Matty Matty

So, where a lower case letter is followed by an upper case letter, a space should be inserted.

Thanks,

Matty

 

[pgc01]

Hi Matty

This is a udf to split CamelCase text.

For ex.:

CamelCaseTextExample

becomes:

Camel Case Text Example

Function CamelCase(s As String)
  
With CreateObject("vbscript.regexp")
    .Pattern = "([a-z])([A-Z])"
    .Global = True
    CamelCase = .Replace(s, "$1 $2")
End With
End Function

Remark: if your case is just like in the example always just 2 words you can also do it with a formula.

 

[Matty]

Hi pgc01,

Thanks for the UDF - I will give this a try.

I'm also interested in the formula solution, as only two words should ever be "stuck together" like in the example.

Thanks,

Matty

 

[shift-del]

Hi Matty

<table valign="middle" colspan="5" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="59,25pt"><col width="60pt"><col width="61,5pt"><col width="63,75pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="5" align="middle">Worksheet 'Tabelle1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td><td align="middle">C</td><td align="middle">D</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="left">MattyMatty</td><td align="right">6</td><td align="left">Matty Matty</td><td align="left">Matty Matty</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td>{=MAX(IF(CODE(MID(A1&REPT("|",20-LEN(A1)),ROW($1:$20),1))<97,ROW($1:$20)))}</td></tr><tr><td>C1</td><td>=REPLACE(A1,1,B1-1,LEFT(A1,B1-1)&" ")</td></tr><tr><td>D1</td><td>=SUBSTITUTE(A1,LEFT(A1,B1-1),LEFT(A1,B1-1)&" ")</td></tr></tbody></table><table style="font-family: Arial; font-size: 8pt; background-color: rgb(255, 255, 255);"><tbody><tr><td style="font-weight: bold;">Contains array formula!</td></tr><tr><td>Do not enter the curly brackets {}.</td></tr><tr><td>Enter the formula with CTRL-SHIFT-ENTER instead of just ENTER.</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Created with Tab2HTML (v2.4.1). ©Gerd alias Bamberg
</td></tr></tbody></table>

 

[mikerickson]

This CSE formula should do it
=REPLACE(A1, 1+MATCH(1, (CODE(MID(A1,COLUMN($B:$AA),1)) < 91)*(96 < CODE(MID(A1,COLUMN($A:$Z),1))), 0), 0, " ")

If the red 0 is changed to 1 it will put a space at the last change to UC.

That formula needs to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac).

 

[Matty]

Thanks for your input shift-del and Mike. Both formulas work well.

Mike,

Could you help me to understand the formula, please? Specifically, the use of "91" and "96" - it's just I've never seen that construct used before.

Thanks

Matty

 

[meldoc]

Hi Matty

I'll be interested in Mike's reply, I think the answer lies in this partial ASCII code snippet.

Excel Workbook
	A	B
1	89	Y
2	90	Z
3	91	[
4	92	\
5	93	]
6	94	^
7	95	_
8	96	`
9	97	a
10	98	b
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B1	=CHAR(A1)

 

[barry houdini]

If your string contains only 1 upper case letter after the first 1 then this formula will also insert a space at the appropriate place

=REPLACE(A1,LOOKUP(2^15,FIND(CHAR(ROW(INDIRECT("65:90"))),A1,2)),0," ")

...doesn't need "array entering"

 

[Matty]

Hi Matty

I'll be interested in Mike's reply, I think the answer lies in this partial ASCII code snippet.

Excel Workbook
	A	B
1	89	Y
2	90	Z
3	91	[
4	92	\
5	93	]
6	94	^
7	95	_
8	96	`
9	97	a
10	98	b
Sheet1

Excel Workbook
Cell	Formula
B1	=CHAR(A1)
Excel 2010 Worksheet Formulas

Hi Brian,

I suspect you're correct on that score. Thanks for responding.

If your string contains only 1 upper case letter after the first 1 then this formula will also insert a space at the appropriate place

=REPLACE(A1,LOOKUP(2^15,FIND(CHAR(ROW(INDIRECT("65:90"))),A1,2)),0," ")

...doesn't need "array entering"

Hi barry houdini,

Very neat solution! Thanks for sharing.

Matty

 

[mikerickson]

correct about the 91, 97
(CODE(MID(A1,COLUMN($B:$AA),1)) < 91)*
(96 < CODE(MID(A1,COLUMN($A:$Z),1))

COLUMN($A:$Z) is one way to refer to the array {1,2,3,...,26}
and COLUMN($B:$AA) is a way to refer to {2,3,4,...,27}

(CODE(MID(A1,COLUMN($B:$AA),1)) < 91)*(96 < CODE(MID(A1,COLUMN($A:$Z),1)) is a way of refering to

(CODE( (n+1)th character) < 91)*(96 < CODE( nth character ))

Which will equal 1 if the nth character is lower case and the n+1th is Upper case, 0 otherwise.

MATCH(1, (CODE(...)*CODE(....), 0) returns the first n where nth character is lc and the n+1th is UC.

The REPLACE function then puts a space at that location.

Note: if the switch from lc to UC might occur after the 26th character, the ranges $A:$Z and $B:$AA should be expanded.