Home > Net >  regex to match several semicolon-word and remplace with #
regex to match several semicolon-word and remplace with #

Time:02-19

I would like to do some automatic translations with spreadsheets and regex, but google is translating all the internal parameter starting with semicolon, for example :title :name but I discover that if I put #title #name it keeps the parameter intact so I can just translate the rest of the line.

So the idea is to remplace all :word to #word but not word: to word# as that it's not a parameter, parameters are always starting with : and followed by a word -> :name

So for example I want to change the following string:

Hola, :name, hoy es :date, reloj a continuación:

Into

Hola, #name, hoy es #date, reloj a continuación:

So I want to create a formula like =REGEXREPLACE(A1, ":[a-zA-Z]*", "#")

But that formula is giving me:

Hola, #, hoy es #, reloj a continuación:

Deleting the parameter.

The ideal regex would be to detect all words starting with : (semicolon) and replacing them with # without affecting the word

CodePudding user response:

You may use REGEXREPLACE with a capture group:

=REGEXREPLACE(A1, ":([a-zA-Z] )", "#$1")
  • Related