Is there a quick way to insert commas after specified words to split addresses, rather than insert each one in find & replace?

[gpedlar]

camdenmarch-june20MANUALREAD.ods

I need to put these on address labels and have no control of the collection.
I have had some advice on methods to do this, but all end up with a lot of manual adjusting to get each part of the address into its own column.

I have found that the best method is to ‘find & replace’ words like London & Road with London, & Road,(with the comma after them)
Is there a method where i can replace all the relevant words (Lane, Terrace , Street etc) at the same time before splitting using ‘text to columns’ with a comma as the seperation value?

Heres hoping!

[ajlittoz]

May be the best way to do this is to export as CSV, use a macro-generator to do the job and finally re-import into Calc.

[dscheikey]

Hi gpedlar,

I think =SUBSTITUTE(B2," London ",", London, ") is what you need!

To be able to use the Split () function in Calc in the future, you can define this yourself as a function.
Please see here: Bugzialla comment

Best regards!

Juergen

[gpedlar]

Thanks for helping, though I have no clue how to define a function !

[dscheikey]

See here:

User Defined Functions