Split file name and path into separate cells in Excel 2010

[Fardringle]

I'm trying to take a listing of about 4,000 file names (with their full paths) and split them so that the file name is in one cell and the rest of the path is in a separate cell.

For example, take c:\users\jon\stuff.txt and make it into two separate cells, one containing C:\users\jon\ and the other cell containing stuff.txt

I have managed to extract the file names using this formula, but my Excel 'skills' are lacking the correct commands/syntax to do the same thing with the remainder of the path.

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


Any suggestions how to modify this formula to return the full path instead of the file name (or a different formula that will get it done)?

 

[Netopia]

Might be easier in two steps.

Step one would be to break it at all "\" (probably use the "text to columns" feature)

Step two would be to use "concatenate" function to put the parts of the path back together in a single cell.

Doing it that way, even though it's two steps, should only take a few minutes.

Joe

 

[Fardringle]

That's a possibility, Joe, but since the paths are of varying lengths (sometimes as many as 10+ subfolders deep), how would I tell the formula to use all possible separated columns for the "concatenate" function, but NOT include the cell that ends up holding the file name?

 

[Gooberlx2]

Usually, I'd probably use some regex find/replace within a text editor like notepad++ first to format things as I'd like them, then use text-to-columns or text import wizard as I would a with any csv.

anyway here's what I came up with using excel:

full path
A2 -- c:\users\jon\somedir\stuff.txt

user path
=MID(A2,1,SEARCH("\",A2,LEN("c:\users\")+1))
result: c:\users\jon\

file path
=MID(A2,SEARCH("\",A2,LEN("c:\users\")+1),LEN(A2))
result: \somedir\stuff.txt

It's username agnostic this way as well, in case yours wasn't already.

 

[Charles Kozierok]

Send me the file, I can do it in PSPad in 30 seconds.

 

[Netopia]

Send me the file, I can do it in PSPad in 30 seconds.

Charles, if you've tried them both, I'd be interested in your thoughts on PSPas vs Notepad++

Joe

 

[Charles Kozierok]

Haven't tried Notepad++ and PSPad annoys me in a number of ways, so maybe I'll check that out.

Of course I haven't updated PSPad in years either.