Replace a string

[kingsushi001]

Good day everyone

Can someone please help me with the following...

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 568"]
<tbody>[TR]
[TD="class: xl63, width: 568"]http://www.xyz.co.za/ProdImg/AA13NDfl1uuL2c.jpg[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 568"]
<tbody>[TR]
[TD="class: xl65, width: 568"]http://www.africaestore.com/esquire/AA13NDfl1uuL2c.jpg[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 568"]
<tbody>[TR]
[TD="class: xl65, width: 568"]http://www.esquireshop.co.za/ProdImg/WDBYFT0020BB.jpg[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 568"]
<tbody>[TR]
[TD="class: xl65, width: 568"]http://www.africaestore.com/esquire/WDBYFT0020BB.jpg

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I need to change A1 and A2 to look like B1 and B2. Basically just replacing before the last /
Is there a function or combination of functions I can use to achieve this?

Any help would be greatly appreciated.

 

[oldbrewer]

="http://www.africaestore.com/esquire/"&mid(a1,search("Img",A1)+4)

not tested

 

[TonyUK72]

My pasting of formulas hasn't been working of late, just remove the first & last speech marks, you can then copy the formula down.

"="http://www.africaestore.com/esquire/"&RIGHT(A1,LEN(A1)-FIND("Img/",A1)-3)"

 

[kingsushi001]

="http://www.africaestore.com/esquire/"&mid(a1,search("Img",A1)+4)

not tested

This worked.
Than you very much

 

[TonyUK72]

This worked.
Than you very much

Are you sure, I couldn't get this to work. That's why I opted for the FIND, LEN & RIGHT formula.

 

[oldbrewer]

="http://www.africaestore.com/esquire/"&mid(a1,search("Img",A1)+4,99)

I forgot the ,99 so it could not have worked, could it ?

 

[TonyUK72]

Why 99?


**Feeling dumb for asking**

 

[sparkerk1]

Dear Sir,

I have a query i want extract specific number from the below , the condition should be that its should ignore the fare given in USD and give out the number in inr only, the query should run in the first 2 lines only as the 2nd line contains the fare which needs to be extracted.

Appreciate your your kind help.


USD 391.00 13OCT18BOM CX X/HKG CX TAO391.00NUC391.00
INR 25430 END ROE1.000000
INR 3187YR XT INR 850YM INR 258IN INR 154WO INR 584G3
INR 1431K3 INR 376I5
INR 2222XT
INR 32270

 

[oldbrewer]

why 99 - don't feel dumb - it is how many characters to show - needs to be big enough - could be 88 or 150 or 2000 it does not matter

 

[oldbrewer]

sparkerk1 - that is what you start with - show us what you want to end up with - are those 6 lines all in one cell (really you needed to start a new thread but you are new - welcome - we will help you