Can this be done?

[Steve_B]

Hi All

I have a string of text in cell A1 similar to this : C:\start\123\solidworks.slddwg (This can vary in length and contents)

In Cell B2 I have a formulae to manipulate the string to get: C:\start\123
I need to get solidworks.slddwg into Cell C2

Any ideas

Steve

 

[north19701]

=MID(A1,14,LEN(A1)-14)

would this work for you?

 

[Aladin Akyurek]

If you have in B2 a formula giving you C:\start\123, then in C2 enter:

=SUBSTITUTE(A1,B2&"\","")

 

[Steve_B]

Thank but unfortunately this removes text from both ends of the string

Steve

 

[Steve_B]

Thanks Aladin but this returns a string without any of the \ \

Steve

 

[Aladin Akyurek]

...this returns a string without any of the \ \...

What you have in B2 might be the culprit. Try...

Book9
	A	B	C	D
1	C:\start\123\solidworks.slddwg
2		C:\start\123	solidworks.slddwg
3
Sheet1

B2:

=SUBSTITUTE(A1,"\"&C2,"")

C2:

=MID(A1,SEARCH(CHAR(127),SUBSTITUTE(A1,"\",CHAR(127),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))

 

[Ahnold]

=RIGHT(A1,(LEN(A1)-1)-LEN(B2)) will produce "solidworks.slddwg"
=RIGHT(A1,LEN(A1)-LEN(B2)) will produce "/solidworks.slddwg"

 

[Patrick Alexander]

you have to find the latest "\"
therefore i created a new function prf
then new function is

Function prf(tekst)
x = Len(tekst)
For y = 1 To x
txt = Mid(tekst, y, 1)
If txt = "\" Then
prf = y
End If
Next y
End Function


when you put your data in A1
in B1 you write =left(A1,prf(A1)) you get C:\start\123\
in B2 you write =right(A1,(len(A1)-prf(A1)) you get solidworks.slddwg

you can you use this for every kind of directory string he will divide it

regard

 

[Steve_B]

Thanks all

Aladin Still no joy B2 now returns string with no \\ althought C2 is now correct.

Ahnold I tried both options again with no joy.

Patrick I will try your suggestion later.

 

[Aladin Akyurek]

...
Aladin Still no joy B2 now returns string with no \\ althought C2 is now correct...

It seems HtmlMaker suppresses "\"...

Try again will you with:

=SUBSTITUTE(A1,CHAR(92)&C2,"")

in B2 and

=MID(A1,SEARCH(CHAR(127),SUBSTITUTE(A1,CHAR(92),CHAR(127),LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(92),""))))+1,LEN(A1))

in C2...