Split capital words

forest80 · October 4, 2021, 3:23pm

i have a messy sheet where names have become joined together -MsandMr MariaPapadimaAlexandrosIlias
. All i need to do is split them at each capaital letter to sort it out. How do I do this?

thanks

erAck · October 4, 2021, 3:38pm

With MsandMr MariaPapadimaAlexandrosIlias in A1

=REGEX(A1;"([^ ])([[:upper:]])";"$1 $2";"g")

That keeps Msand though… but it inserts a blank before each upper letter that is not preceded by a blank already.

forest80 · October 4, 2021, 3:51pm

thanks, where do i type that?

forest80 · October 4, 2021, 3:53pm

i can do the ‘and’ by search and replace and with (space)and

erAck · October 4, 2021, 3:53pm

Well, as your question was for Calc, I’d presume into some Calc cell other than A1, no? …

erAck · October 4, 2021, 3:58pm

But you can do a regular expression Find&Replace (Ctrl+H) as well.

Find: ([^ ])([[:upper:]])
Replace: $1 $2
[x] Match case
Other options: [x] Regular expressions

forest80 · October 4, 2021, 4:11pm

Brilliant thanks. My knowledge is limited, and I don’t know about cell codes so that solution worked for me.

Much appreciated !