A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Is this a trim issue



 
 
Thread Tools Display Modes
  #1  
Old March 21st, 2010, 08:02 PM posted to microsoft.public.excel.newusers
Box666
external usenet poster
 
Posts: 60
Default Is this a trim issue

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.
  #2  
Old March 21st, 2010, 08:50 PM posted to microsoft.public.excel.newusers
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Is this a trim issue

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
  #3  
Old March 21st, 2010, 09:51 PM posted to microsoft.public.excel.newusers
Alejandro Medinilla elMedex
external usenet poster
 
Posts: 7
Default Is this a trim issue

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(A 1))-1))


Regards

elMedex

"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.
.

  #4  
Old March 22nd, 2010, 02:21 AM posted to microsoft.public.excel.newusers
Box666
external usenet poster
 
Posts: 60
Default Is this a trim issue

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
  #5  
Old March 22nd, 2010, 03:07 AM posted to microsoft.public.excel.newusers
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default 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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:19 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.