Can this be done?

Steve_B

Board Regular
Joined
Feb 9, 2004
Messages
86
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
If you have in B2 a formula giving you C:\start\123, then in C2 enter:

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

Steve_B

Board Regular
Joined
Feb 9, 2004
Messages
86
Thank but unfortunately this removes text from both ends of the string

Steve
 

Steve_B

Board Regular
Joined
Feb 9, 2004
Messages
86

ADVERTISEMENT

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

Steve
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Feb 20, 2004
Messages
636

ADVERTISEMENT

=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
Joined
Feb 23, 2004
Messages
75
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
Joined
Feb 9, 2004
Messages
86
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,651
Members
414,399
Latest member
Ninjee

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top