Replace part of columns string value

Question

I have a column with values like /i/t/item_xxl_12063874_18123233.jpg. I need a formula to get a string value item_xxl_12063874_18123233.jpg.

Thanks @OrkhanAlikhanov. I don't know how to write this sort of formula. — abu abu
– abu abu, Commented Jun 7, 2017 at 3:34

A.S.H · Accepted Answer · 2017-06-06 14:20:10Z

1

If it's always to remove the first 5 characters, simply

=MID(A1, 6, 9999)

BUT if it is about retrieving what is after the last /, with a random amount of characters before it, then it is a little more tricky:

 =TRIM(RIGHT(SUBSTITUTE(A1, "/", REPT(" ", 300)), 300))

answered Jun 6, 2017 at 14:20

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

abu abu Over a year ago

Thanks A.S.H. Your first formula works for me. Thanks

Solar Mike · Accepted Answer · 2017-06-06 19:22:45Z

0

Another approach is to find the text "item" as follows:

=MID(A1,FIND("item",A1,1),LEN(A1))

you can change the text you search for as necessary and it does not matter how many are before the text you search for as long as it only occurs once.

answered Jun 6, 2017 at 19:22

Solar Mike

8,4255 gold badges23 silver badges34 bronze badges

Collectives™ on Stack Overflow

Replace part of columns string value

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related