CODEWITHSUNDEEP
google-sheets
robetus
robetus

Reputation: 501

replace everything after a character in Google spreadsheet

I did some searching and in openoffice and excel it looks like you can simply add an * at the beginning or end of a character to delete everything before and after it, but in Google spreadsheet this isn't working. Does it support this feature? So if I have:

keyword USD  0078945jg .12 N N 5748 8

And I want to remove USD and everything after it what do I use? I have tried:

USD* and (USD*) with regular expressions checked

But it doesn't work. Any ideas?

Upvotes: 12

Views: 80328

Answers (4)

benbland
benbland

Reputation: 111

To add to the answers here, you can get into trouble when there are special characters in the text (I have been struggling with this for years).

You can put a frontslash \ in front of special characters such as ?, + or . to escape them. But I still got stuck when there were further special characters in the text. I finally figured it out after reading find and replace in google sheets with regex.

Example: I want to remove the number, period and space from the beginning of a question like this: 1. What is your name?

Go to Edit → Find and replace

In the Find field, enter the following: .+\. (note: this includes a space at the end).

Note: In the Find and replace dialogue box, be sure to check "Search using regular expressions" and "match case". Leave the Replace field blank.

The result will be this text only: What is your name?

Screenshot of the process

Upvotes: 1

KyleMit
KyleMit

Reputation: 29987

In addition to options that would be available in Excel (LEFT + FIND) pointed out by pnuts, you can use a variety of regex tools available in Google Sheets for text searching / manipulation

For example, RegexReplace:

=REGEXREPLACE(A1,"(.*)USD.*","$1")
  • (.*) <- capture group () with zero or more * of any character .
  • USD.* <- exact match on USD followed by zero or more * of any character .
  • $1 <- replace with match in first capture group

enter image description here

Upvotes: 8

AdamL
AdamL

Reputation: 24609

The * quantifier just needs to be applied to a dot (.) which will match any character.

enter image description here

To clarify: the * wildcard used in certain spreadsheet functions (eg COUNTIF) has a different usage to the * quantifier used in regular expressions.

Upvotes: 14

pnuts
pnuts

Reputation: 59475

Please try:

SO25376275 first example

and also have a look at.

For spaces within keyword I suggest a helper column with a formula such as: 

=left(A1,find("USD",A1)-1)

copied down to suit. The formula could be converted to values and the raw data (assumed to be in ColumnA) then deleted, if desired.

SO25376275 second example

Upvotes: 0

Related Questions