0

I have an issue with Google Spreadsheets. I export bulk address lists from my eBay orders into Google Spreadsheet to be able to export as a CSV file to my label printer software for printing. The issue I have is eBay adds in a random string of text which look like this ebay:jlxjpd5 in between the name and address of each order. I don't want this text there and I have been manually deleting it from every order for a long time. I have attached a photo for reference of how the addresses look. Whenever I copy paste the address from eBay to Google Spreadsheet it is always in the format shown in the picture.

I know how to remove the word eBay and replace it with an empty space but then the random string of letters still appears. Basically I need a formula or method to be able to remove the second string/line of text/numbers in all cells or to remove 12 characters from the second string/line of text within each cell. In the below example I need all "ebay:hly9yhg, ebay:blypnh1, ebay:bly5rds" removed from the entire spreadsheet. The string of text after the word "eBay:" is random and changes for each order and is always 7 characters (12 characters including word eBay and colon). I deal with large amount of orders so deleting the words take up a lot of my time. If someone has a solution it will save me so much time so any help would be appreciated.

Note: The names and addresses are not real and made up for the sake of this question, no personal info has been revealed.

enter image description here

Improve this question

1 Answer 1

Reset to default
1

You could try this formula in B1:

=index(regexreplace(A1:A,"ebay:\w+\n",""))

replace

Improve this answer
3
  • Thank you so much, this works! I was wondering are you able to do a formula to keep the original spacing. example, Jacob T then the line underneath is empty and the 3rd and 4th line would be the address. The spacing between the name and address looks a lot better when I print out the name and address and also helps with the mailing machines to read the address.
    – petebruss
    May 31, 2022 at 6:47
  • Never mind, I figured it out and was able to edit your formula to make it work. I ended up removing the \n and it keeps the original spacing. I used: =index(regexreplace(A1:A,"ebay:\w+","")) Thank you so much for helping me out. This is a lifesaver and will save me countless hours and hours of work
    – petebruss
    May 31, 2022 at 7:09
  • yes, the /n was used to eliminate the second line that became empty
    – Daniele
    May 31, 2022 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.