Is this a trim issue

  • Thread starter Thread starter Box666
  • Start date Start date
B

Box666

I have been given somebody's library to catalogue and provide a search
engine for. At the moment it is in text format in column A.

The following is an example.

G:\Books\N O P\Nigel R Clough\Nigel R Clough - How To Make and Use
Mirrors.pdf

Or sometimes it appears as
G:\Books\N O P\Nigel R Clough - How To Make and Use Mirrors.pdf

So I want to move everything to the right of the last “\” into column
B and the rest into column C. So in the first example above:-

Nigel R Clough - How To Make and Use Mirrors.pdf would be in Column B
And
G:\Books\N O P\Nigel R Clough\ would be on Column C.

While in the 2nd example
Nigel R Clough - How To Make and Use Mirrors.pdf would be in Column B
And
G:\Books\N O P\ would be on Column C.

Any help in this matter would be much appreciated.
 
I have been given somebody's library to catalogue and provide a search
engine for. At the moment it is in text format in column A.

The following is an example.

G:\Books\N O P\Nigel R Clough\Nigel R Clough - How To Make and Use
Mirrors.pdf

Or sometimes it appears as
G:\Books\N O P\Nigel R Clough - How To Make and Use Mirrors.pdf

So I want to move everything to the right of the last “\” into column
B and the rest into column C. So in the first example above:-

Nigel R Clough - How To Make and Use Mirrors.pdf would be in Column B
And
G:\Books\N O P\Nigel R Clough\ would be on Column C.

While in the 2nd example
Nigel R Clough - How To Make and Use Mirrors.pdf would be in Column B
And
G:\Books\N O P\ would be on Column C.

Any help in this matter would be much appreciated.
Click to expand...

B1:

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",255)),255))

C1:

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))



--ron
 
first create a function to reverse the string

Function InvertStr(str As String)
InvertStr = StrReverse(str)
End Function

then use that function as formula

b1:
=LEFT(A1,LEN(InvertStr(A1))-FIND("\",InvertStr(A1))+1)
c1:
=InvertStr(LEFT(InvertStr(A1),FIND("\",InvertStr(A1))-1))


Regards

elMedex
 
B1:

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",255)),255))

C1:

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"­\","")))))

--ron- Hide quoted text -

- Show quoted text -
Click to expand...

Ron,
Thank you B1 works great but C1 brings up a #VALUE! error
 
Ron,
Thank you B1 works great but C1 brings up a #VALUE! error
Click to expand...

I don't know why or how the C1 formula got changed from what I posted. Must be
an idiosyncrasy of how you are accessing the newsgroup.

In any event, there should not be a hyphen prior to the slash in the last
substitute function.

Perhaps if I put it on two lines, it'll "come through" properly:

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(
A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

--ron
 
Back
Top