Home > OS >  how to replace only the first whitespace in string with a dash with regexreplace in google sheets?
how to replace only the first whitespace in string with a dash with regexreplace in google sheets?

Time:04-29

Trying to replace: "abcd efgh ijkl" with "abcd-efgh ijkl" in google sheets. I tried:

=REGEXREPLACE(C5,"(?:[A-Za-z] )(\s)","\-$1")

but it doesn't work. I think I'd need to use beg/end anchors (^$)but this failed too. Any idea?

CodePudding user response:

You can use online tools such as https://regex-generator.olafneumann.org/ to easily create regex patterns.

Nevertheless, In your example, WORKING ON EDIT (ORIGINAL QUESTION WAS EDITED) would work. (Tested on https://regexr.com/ and google sheets)

CodePudding user response:

Capture the leading part and put it back in the replacement:

=REGEXREPLACE(C5, "^(.*?)\s", "$1-")

This captures everything between the start to the first whitespace, consuming the whitespace, and replaces it with the captured content and a dash - effectively replacing the space with a dash.

The important part of the regex is the ? after .*, which makes the .* reluctant, which matches as little as possible. As opposed to plain .*, which matches as much as possible (ie greedy).

  • Related