Excel Replace Using Formula

2
votes

I'm trying to Replace a Cell with other Cell Text, the think is i need to removed some text from an Image URLs using Excel Formula.

Image URL : domain.com/images/products/63/63/19787/2/279/image-name.jpg

Text that i needed to be removed is 279/. But i need it to be removed at the exact place like domain.com/xxx/xxx/xxx/xxx/xxx/x/279/image-name.jpg between URL and Image name.

I've tried to split it first with this Formula

=MID(A1,FIND("|",SUBSTITUTE(A1,"/","|",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1)),LEN(A1))

Result after i used the Formula

/279/image-name.jpg

and i tried using Replace Formula to replace text from other cell text. Before that i removed the /279 from the result so its only /image-name.jpg now.

=REPLACE(A1, FIND(B1,A1), 4, C1)

But its keep giving me double result in the end of the text like this

enter image description here

domain.com/images/products/63/63/19787/2/image-name.jpg/image-name.jpg

result should be

domain.com/images/products/63/63/19787/2/image-name.jpg - without 279/

is there any problem with the Replace Formula ? or is there any other simpler Formula to make it work ?

3
excelexcel-formula
is it always 279? - Mech
Change last parameter in =REPLACE(A1, FIND(B1,A1), 4, C1) to "". Because you want to replace the found text with an empty string. - Luuk
Yes its always 279 in exact position before image-name.jpg, but there are sometimes more 279 in different position, if i used Replace all instead it will delete the other 279 on the URL. - rullymnh

3 Answers

2
votes

To find the location of the next to last slash:

=FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1))

If the contents of that node will always be three characters, you can use replace:

=REPLACE(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1)),FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1)),"")

If the contents of that node will be a variable number of characters, then we first return the part up to that node, and concatenate with the last node:

=LEFT(A1,FIND(CHAR(1),LEFT(SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1),99))) &
TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99))

EDIT Logic added to ensure that the next to last node is equal to 279

If you need to confirm that the next to last node contains 279, you can check it with:

=ISNUMBER(N(FIND("/279/",MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1)),99))=1))

Using that as part of an IF will return the original string if 279 is not the contents of that node, and replace it only if it is:

=IF(ISNUMBER(N(FIND("/279/",MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1)),99))=1)),
LEFT(A1,FIND(CHAR(1),LEFT(SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1),99))) &
TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),A1)
2
votes

If you happen to have access to TEXTJOIN then use:

=TEXTJOIN("/",,FILTERXML("<t><s>"&SUBSTITUTE(A1,"/","</s><s>")&"</s></t>","//s[not(position() = last()-1)]"))

And if you need to check if it's equal to '279' before removal:

=TEXTJOIN("/",,FILTERXML("<t><s>"&SUBSTITUTE(A1,"/","</s><s>")&"</s></t>","//s[not(position() = last()-1 and .='279')]"))

If you don't have access to TEXTJOIN then you have a fine alternative by @RonRosenfeld.

Another option would be to use REPLACE():

=REPLACE(A1,FIND("|",SUBSTITUTE(A1,"/","|",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1)),FIND("|",SUBSTITUTE(A1,"/","|",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))-FIND("|",SUBSTITUTE(A1,"/","|",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1)),"")
1
votes

You want to use the SUBSTITUTE() function.

The below finds "/279/image-name.jpg" and replaces it with "/image-name.jpg". The added /image-name.jpg will ensure other instances of /279 will remain unaltered.

A1 value = "domain.com/images/products/63/63/19787/2/279/image-name.jpg"

=SUBSTITUTE(A1,"/279/image-name.jpg","/image-name.jpg")

output:

domain.com/images/products/63/63/19787/2/image-name.jpg