Comments on: Google Sheets: remove the same text or certain characters from multiple cells at once

Learn formulas and formula-free ways for Google Sheets to trim whitespaces, remove special symbols (even the first/last N characters) and the same text strings before/after certain chars from multiple cells at once. Continue reading →

Comments page 2. Total comments: 97

  1. Is it possible to use this function to take a cell with existing information (A2) and add the information in another cell (combine A2 and B2) to create a new list, then use it again to take that new list (C2) and remove the information in another cell (D2) to generate a new list?

    I tried to add a picture to show it better, but it won't let me. This is a link to a Google Sheet that tries to show it visually: https://docs.google.com/spreadsheets/d/1hlLuvSuBQSV04dxTkyWU9SO1W13sfnEdRVlfCzkONNk/edit#gid=0

    Thank you!!!

  2. Hi there! So I am trying to create a column where I have a pallet count. I have tried all of the suggested formulas and am still struggling. I have a few examples below. I exported information from a program into excel, but need it for a project I am doing in Google Sheets. I had struggled to create formats and formulas to make this work :(. When I export the information, it appears like below.

    I would like to be able to paste this into a google sheets column, with just the beginning number in front of Pallets, and get rid of everything else. Do you have any suggestions?

    Thank you!

    27Pallets801.58lbs1884.1ft3
    1Pallet37.5lbs43.84ft3

  3. Hi!
    Is there a way to combine RIGHT and LEFT in one formula cell?
    I need to do this...
    =LEFT(A1,LEN(A1)-17) and =RIGHT(A1,LEN(A1)-34), all in one cell.

    Is there a chance of doing this?
    I tried combinen them by brackets, but it doesn't work either
    =(RIGHT(A1,LEN(A1)-34)),(LEFT(A1,LEN(A1)-17))

  4. Hi Natalia,

    I need to delete years and brackets after text e.g DEPARTED, THE (1961). How would I go about doing this so I remain with just DEPARTED, THE

    Thank you!

  5. Hi Natalia
    I am a teacher and I create roll lists for house exams. I need to delete names from the lists as the students are sitting there exams in a separate centre.
    The software will generate the class list for me but I have to remove names and I have to do this manually.
    Is there a formula in Google sheets that I can use where the name is removed. I can easily deal with the empty cell afterwards.
    My hope is that if I can use a formula to remove the names, I can then use that formula for various subjects.
    Thanks
    Kieran

    1. Hi Kieran,

      I'm afraid there's no way to remove the contents of a cell by placing a formula there. Formulas return results to the same cells they are used in, and they can't refer to the same cells they are entered in. In this case, you will need to use Apps Script to avoid manual data processing. You may try to find a solution here – an overview of Google Apps Script with a lot of helpful content and links.

  6. Cell data contains:
    domainname.comForwards to http: //subdomain.domainname.com

    Is there a way to remove everything in a cell from "Forwards to...." to the end?

    What I want to end up with is the beginning part:
    domainname.com

    Thanks

    1. Hello Mario,

      Assuming your cell with that record is A2, use this formula:
      =REGEXREPLACE(A2,"(.*)Forwards to (.*)","$1")

      Look through this part of the article to understand how it works.

  7. This was a very good article. Its explanations were clear and concise. Thank you.

    I have a sheet that has phone numbers with a 1 in front of every number. How can I remove it from every number without removing all the 1's in the list? I tried the left, right and len but not getting results.
    17137996969
    17139565876
    12102265350
    12107042187

  8. hey i have data like

    $1.5 --in same cell
    0.01 -- same cell

    $2
    0.1

    how to split into two column , above method not working

  9. Is there a way to remove " " ?

    For example getting JASON from "JASON"

    Thanks !

  10. Formula to remove the last period of a sentence in a cell?

  11. Thank you, the guide helped a lot...
    Question : how would I specificaly delete characters present between any parenthesis and parenthesis themselves ?
    like:

    Paris (1D4)
    London (11)
    Berlin (abc)...

    Result : "Paris", "London", "Berlin" etc...

  12. Hi, I would like to delete the text in the cells "K7" when I write " yes " in the cells "L7" do you know how I can do that?
    Thanks

  13. Is there a way to limit the number of characters that will print out from a column when doing a mail merge. My sheet is to print out spine labels. I have Dewey decimal #, authors last name, and Title. I inputted all data from LibraryThing but I need to limit the Title to maybe the first 20 characters.
    Can you think of any way I can do this for the whole column (934 titles).
    Thanks so much.

  14. Hi! I just wanted to know how to remove text from a column so its just the numbers when I copy in bank statements.
    It looks like:
    D
    Money In, £, 20.00
    Money In, £, 50.00
    Money In, £, 15.00
    etc
    I just want to know how to get rid of the" money in", or how to make the sum without an error.

    1. Hi Lauren,

      Based on how your data looks, I'd advise you to look through the last 3 parts of this blog post. You can use any of the ways described there.

  15. Is there a way to mask a list of names due to confidential reasons, which are contained in a column with names? I would like to keep the first name, and mask the second and third words with asterisks, while keeping the first letter of the second and third words.

    eg
    "Adam John Smith" to "Adam J*** S****
    "Charlie Doe" to "Charlie D**"
    etc

    Many thanks in advance !!!

    1. Hello Calvin,

      I'm afraid masks cannot consider the number of characters. So for your case, you can try a formula like this (assuming the data is in A1):
      =ArrayFormula(JOIN(" ", ARRAY_CONSTRAIN(SPLIT(A1, " "), 1, 1), REGEXREPLACE(SPLIT(REGEXREPLACE(A1, "^.*?\s+?", ""), " "), "[a-z]", "*")))

      1. Excellent and thanks a lot!

  16. +1 1520-222-9195
    +1 1773-443-3769
    +1 1480-547-5498
    +1 1480-205-2468
    +1 1602-339-0394
    +1 1480-226-6228
    +1 1623-428-4605
    +1 1602-921-4482
    +1 1928-830-9057
    +1 1602-482-9678
    +1 1602-330-6397
    +1 1602-394-1963
    +1 1970-317-1135
    +1 1602-371-7951
    +1 1623-313-5432
    +1 1602-551-5827

    Is there a way to remove the number "1" except for those with "+"?

  17. vm2-01.17
    vm2-01.17
    vm2-01.17
    vm2-01.17
    vm2-01.17
    vm2-01.17
    busy
    accepted
    vm2-01.17
    vm2-01.17
    vm2-01.17
    vm2-01.17
    accepted
    busy

    I want to remove the ones that contains vm

  18. Hi I have a data set like this.
    355
    365
    957
    0563R
    043
    0576R
    754
    523
    0376R
    452
    054
    0965R
    0573R
    678

    I want to remove 0 value only on the digits end with 'R'. Any specific way to do this without doing each manually. Thanks

    1. Hi there,

      Well, you can wrap REGEXREPLACE in ArrayFormula and it will calculate all rows at once. For example, for these 14 rows this formula will do:
      =ArrayFormula(IFERROR(REGEXREPLACE(A1:A14,"0(.*)R","$1R"),A1:A14))

  19. Thank you lots. Is there a way to replace/substitute numbers before or after a number or date, eg p5/12/2021 please?

    1. Hello Yohance,

      Please give me an example of how your record may look like before and should look after.

  20. I am cleaning up a string which contains *, commas, some other special characters as well as "[9]" in front of name. Every other character was handled except [ ] . While I could introduce an extra substitute function to handle this, is there as way to handle [ ] inside the formula contained in [ ] in one go ?

    1. Hello Nitesh,

      Please provide the exact formula you're using so I could suggest a way to improve it.

  21. Hello! I am using a join formula to combine names from one column with the names from another column with an & in-between. For some of the rows, they do not contain a name in the second column so the formula is creating a name followed by an & with nothing following it. Is there a way to remove the &'s that do not have a name following them while leaving the ones that do alone?

  22. I appreciate this guide. Is there a way to remove those numbers before or after a person’s name (e.g., Carol20, 123Jason) please?

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)