Can this be done?

Steve_B

Board Regular
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

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

north19701

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

would this work for you?

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

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

Steve_B

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

Steve

Steve_B

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

Steve

MrExcel MVP
Steve_B said:
...this returns a string without any of the \ \...

What you have in B2 might be the culprit. Try...
Book9
ABCD
1C:\start\123\solidworks.slddwg
2C:\start\123solidworks.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

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

Patrick Alexander

Board Regular
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

Board Regular
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.

MrExcel MVP
Steve_B said:
...
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...

Replies
9
Views
40
Replies
3
Views
74
Replies
9
Views
99
Replies
4
Views
117
Replies
6
Views
133

1,171,053
Messages
5,873,480
Members
432,981
Latest member
DMcDaniel

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.

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

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