Is this a trim issue
On Sun, 21 Mar 2010 18:21:44 -0700 (PDT), Box666
wrote:
On 21 Mar, 19:50, Ron Rosenfeld wrote:
On Sun, 21 Mar 2010 12:02:35 -0700 (PDT), Box666
wrote:
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.
B1:
=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",255)),255))
C1:
=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LE N(A1)-LEN(SUBSTITUTE(A1,"*\","")))))
--ron- Hide quoted text -
- Show quoted text -
Ron,
Thank you B1 works great but C1 brings up a #VALUE! error
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
|