Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Remove data from cell

This is a discussion on Remove data from cell within the Excel Questions forums, part of the Question Forums category; I have 2000 cells that contain 2 sets of information that I would like removed from the cell. I need ...

  1. #1
    New Member
    Join Date
    May 2013
    Posts
    20

    Default Remove data from cell

    I have 2000 cells that contain 2 sets of information that I would like removed from the cell. I need help with creating a formula that will remove the information that I do not need so that I can copy it down and remove them from all my cells. Here is an example of one of the cells.

    ITEMIMAGEURL1=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/8004608203650__1.jpg,ITEMIMAGEURL2=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/8004608203650__2.jpg

    I need the first part of the cell removed "ITEMIMAGEURL1=" and the middle part "ITEMIMAGEURL2=" All other information would remain the same.

    Can someone please help me with a formula that would remove both and leave the rest intact?

  2. #2
    Board Regular kevatarvind's Avatar
    Join Date
    Mar 2013
    Location
    India
    Posts
    1,034

    Default Re: Remove data from cell

    can you give some more example like the first text whether always before be = and 2nd after , or it can change ?

  3. #3
    New Member
    Join Date
    May 2013
    Posts
    20

    Default Re: Remove data from cell

    Quote Originally Posted by kevatarvind View Post
    can you give some more example like the first text whether always before be = and 2nd after , or it can change ?
    Here are more examples. The information I want removed does not change. It is always ITEMIMAGEURL1= and ITEMIMAGEURL2= that I need removed.

    ITEMIMAGEURL1=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474635002727__1.jpg,ITEMIMAGEURL2=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474635002727__2.jpg
    ITEMIMAGEURL1=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474630291140__1.jpg,ITEMIMAGEURL2=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474630291140__2.jpg
    ITEMIMAGEURL1=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474630287747__1.jpg,ITEMIMAGEURL2=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474630287747__2.jpg
    ITEMIMAGEURL1=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474630335646__1.jpg,ITEMIMAGEURL2=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474630335646__2.jpg
    ITEMIMAGEURL1=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474635003960__1.jpg,ITEMIMAGEURL2=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474635003960__2.jpg

  4. #4
    Board Regular kevatarvind's Avatar
    Join Date
    Mar 2013
    Location
    India
    Posts
    1,034

    Default Re: Remove data from cell

    if those 2 word always same then you can simply find and replace them or if you want formula then try below one assuming data start in A1 then drag down
    =SUBSTITUTE(SUBSTITUTE(A1,"ITEMIMAGEURL1",""),"ITEMIMAGEURL2","")

  5. #5
    New Member
    Join Date
    May 2013
    Posts
    20

    Default Re: Remove data from cell

    Here are more examples. The information I want removed does not change. It is always ITEMIMAGEURL1= and ITEMIMAGEURL2= that I need removed.

    ITEMIMAGEURL1=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474635002727__1.jpg,ITEMIMAGEURL2=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474635002727__2.jpg
    ITEMIMAGEURL1=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474630291140__1.jpg,ITEMIMAGEURL2=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474630291140__2.jpg
    ITEMIMAGEURL1=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474630287747__1.jpg,ITEMIMAGEURL2=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474630287747__2.jpg
    ITEMIMAGEURL1=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474630335646__1.jpg,ITEMIMAGEURL2=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474630335646__2.jpg
    ITEMIMAGEURL1=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474635003960__1.jpg,ITEMIMAGEURL2=http://d3d71ba2asa5oz.cloudfront.net/50000221/images/3474635003960__2.jpg

  6. #6
    Board Regular kevatarvind's Avatar
    Join Date
    Mar 2013
    Location
    India
    Posts
    1,034

    Default Re: Remove data from cell

    Did You Check my post no #4 ?

  7. #7
    New Member
    Join Date
    May 2013
    Posts
    20

    Default Re: Remove data from cell

    Its not that simple because I also want to remove the "=" at the end of each. The formula errors out because I am also trying to remove the "=" at the end of the 2 strings I want removed. If you notice carefully, you will see both strings I want removed have an equal sign at the end. How woud you handle that? The specific error I get is "if you are not trying to enter a formula, avoid using an equal sign )=) or precede it with a single quotation mark (')

  8. #8
    New Member
    Join Date
    May 2013
    Posts
    20

    Default Re: Remove data from cell

    Any ideas on how to overcome the "=" at the end of each string I want to remove?

  9. #9
    New Member
    Join Date
    May 2013
    Posts
    20

    Default Re: Remove data from cell

    Got it to work. Thank you so much

  10. #10
    New Member
    Join Date
    May 2013
    Posts
    20

    Default Re: Remove data from cell

    One more question

    What would I need to add to the formula in order for it to give me a result of a blank cell if it sees the word "photobucket" anywhere? For example below, this
    string has the word "photobucket" in it and I would like a result of a blank cell. What do I add to the formula you provided?

    http://d3d71ba2asa5oz.cloudfront.net/50000221/images/8004608203650__1.jpg,http://d3d71ba2asa5oz.cloudfront.net...8203650__2.jpg

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com