substitute words in a column

excel substitute multiple characters
“find and replace” multiple words in word from excel list
excel replace character
find and replace in word table
find and replace in excel
find and replace with nothing
excel find and replace in column
excel substitute array
a <- data.frame(text = c("hello <firstname> what do you wish to order today?", "I don't understand this. Can you repeat"))

I want to substitute - "hello " and "Can you repeat" with blank so that I get remaining part of text.

How do I give specific list of words that I want to substitute with blank. Here specific word is "hello and "can you repeat". Like this , there are many words in my dataframe.

Expected output in dataframe:

[1] what do you wish to order today?

[2] I don't understand this.

One way with gsub:

#add the words to remove in an atomic vector
to_remove <- c('hello', 'Can you repeat')
#paste the words together and remove with gsub
gsub(paste(to_remove, collapse = '|'), ' ', a$text)
#[1] "  <firstname> what do you wish to order today?"
#[2] "I don't understand this.  "

As per @Sotos comment, when dealing with text, it is probably a good idea to lower case the text and get rid of trailing spaces:

trimws(paste(to_remove, collapse = '|'), '', tolower(a$text)))

substitute words in a column, Use the vectorised str method replace : In [30]: df['range'] = df['range'].str.replace(', ','-') df Out[30]: range 0 (2-30) 1 (50-290). EDIT. So if we look� The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE ("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.

There is a function called gsub which you can use. It looks at string given a pattern then replaces with the desired output.

gsub(pattern, replacement, x, ignore.case = FALSE, perl = FALSE,
    fixed = FALSE, useBytes = FALSE)

Example:

gsub("hello", "", a$text)

Then if you like, you can save the output to as a new column or new variable.

There are some additional things with gsub being case sensitive and what not, but those can be found here. This also has some additional examples.

How to replace text in a column of a Pandas dataframe?, To find the nth occurrence of a character in a text string, you can use a formula based on the FIND and SUBSTITUTE functions. In the example shown, the formula� SUBSTITUTE (text, old_text, new_text, [instance_num]) The first three arguments are required and the last one is optional. Text - the original text in which you want to substitute characters. Can be supplied as a test string, cell reference, or a result of another formula.

The gsub solutions work as well, here is a tidyverse solution.

require(tidyverse)
b <- a %>% mutate(
    text_new = str_remove_all(text, c("hello <firstname>", "Can you repeat"))
  )
b

How to use the Excel SUBSTITUTE function, Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every � Can you repeat")) I want to substitute - "hello " and "Can you repeat" with blank so . Stack Overflow. Products Customers; Use cases; Stack Overflow

SUBSTITUTE function - Office Support, The text you want to replace old_text with. instance_num, (optional) The occurrence of old_text you want to replace. If omitted, every instance of� Excel has a great built in function called SUBSTITUTE which allows you to find one bit of text within another text string and substitute it for another bit of text. Copy and paste this table into cell A1 in Excel In the above example we can use the SUBSTITUTE function to replace all instances of apples with cookies using the following formula.

SUBSTITUTE function (DAX), Click Find Next to locate the first occurrence or click Find All to display a list of all occurrences. You can sort the results of a Find All search by clicking a column� Replace specific column “words” into number or blank. Ask Question Asked 9 years, 4 months ago. Active 7 years, 9 months ago. Viewed 33k times 5. 3. Input table

How to Use Excel 2010's Find and Replace Feature, The fourth argument (Instance_number) allows you specify which occurrence of the old_text you want to replace. If left empty, as with this example, it will replace � Column A is full cells with lots of text. Column B is full of my 'keywords' that I want to extract, much like the 'Original Value' in this formula. Column C needs to be the words from Column A that match the 'keywords' in Column B. Ideally they would be separated by commas with NO spaces, but I can use tools to insert these.

Comments
  • Please fix your example. It throws error. Maybe you are looking for this trimws(gsub('hello|can you repeat', '', tolower(a$text))) though I am not sure what happened to <firstname>
  • Related
  • couple of suggestions. It is probably better to keep/convert everything to lower case. Also you can wrap everything in trimws to get rid of leading/trailing spaces - See my comment above
  • @Sotos Yeah I agree to both. This is the safest technique when dealing with text. I guess I wanted to show the answer only to what the OP has asked. I ll add.