Remove data from cell

verbena

New Member
Joined
May 5, 2013
Messages
33
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

<tbody>
</tbody>

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?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
can you give some more example like the first text whether always before be = and 2nd after , or it can change ?
 
Upvote 0
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

<colgroup><col></colgroup><tbody>
</tbody>
 
Upvote 0
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","")
 
Upvote 0
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

<tbody>
</tbody>
 
Upvote 0
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 (')
 
Upvote 0
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/50000221/photobucket/images/8004608203650__2.jpg

<colgroup><col></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top