Split mixed upper and lowercase in the same word into two lines

user_2371547 0
Viewed 86

I have a string in cells that is lacking new lines.

It looks like this:

Text Text TextText Text Text T5df Tdfcv TextNeu

In other words:

If there is a change from Lowercase to Uppercase within a word, this is where a new line should be inserted as \n.

So the example would convert to

Text Text Text
Text Text Text T5df Tdfcv Text
Neu

Resp.:

Text Text Text\nText Text Text T5df Tdfcv Text\nNeu

I found

String[] r = s.split("(?=\\p{Lu})");

I tried REGAUS(F2;"(?=\\p{Upper})";"\n";"g") yet I get a 502, as something is wrong with the regex.

Which formula do I need for calc to do this?

openoffice-calc regex
1 Answers

With english formula names, the following formula will do the trick:

=REGEX(A1;"([:lower:])([:upper:])";"$1"&CHAR(10)&"$2";"g")

Same on multiple lines for sake of readability:

    =REGEX(
        A1;
        "([:lower:])([:upper:])";
        "$1" & CHAR(10) & "$2";
        "g"
    )

It matches a lower-case letter followed by an upper-case letter, and inserts a newline using the CHAR() function.

You'll have to adapt the line heigth manually, otherwise you will see only "Neu" (the last line).

For localised formula names (german), it would be:

=REGAUS(A1;"([:lower:])([:upper:])";"$1"&ZEICHEN(10)&"$2";"g")

I would have expected that inserting "\n" should work, too, but i did'nt manage got get it working, thus the recourse to CHAR(10).

user_3425460
Related