Here is my situation. I hav a macro that inputs into excel all the file names in a directory and I get results like this in coulmn A. There will not always be the same number of files, it changes each tim

:\ab123
:\df113
:\gh234

My problem comes in editing the list - I need to first remove all of the ":\" from each cell (file name), and then I need to get the last value in the name to sort the files, in this case it would be





and I would want to but those values in column B

I am currently searching through google but any help would be greatly appreciated

Jim

Hi James,

You can use the Replace$() function to remove the :\ from each cell:

Cells(n, 1).Value=Replace$(Cells(n, 1).Value, ":\", "")

And you can use the Right$() function to get the last number:

Cells(n, 2).Value=Right$(Cells(n, 1).Value, 1)

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


James Stephens wrote:
Here is my situation. I hav a macro that inputs into excel all the
file names in a directory and I get results like this in coulmn A.
There will not always be the same number of files, it changes each
time

:\ab1234
:\df1133
:\gh2345

My problem comes in editing the list - I need to first remove all of
the ":\" from each cell (file name), and then I need to get the last
value in the name to sort the files, in this case it would be

4
3
5

and I would want to but those values in column B.

I am currently searching through google but any help would be greatly
appreciated.

Jim

Thanks, worked great, and works to solve some other code issues too.

Thanks again,

Jim

----- Jake Marx wrote: -----

Hi James,

You can use the Replace$() function to remove the :\ from each cell:

Cells(n, 1).Value=Replace$(Cells(n, 1).Value, ":\", "")

And you can use the Right$() function to get the last number:

Cells(n, 2).Value=Right$(Cells(n, 1).Value, 1)

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


James Stephens wrote:
Here is my situation. I hav a macro that inputs into excel all the
file names in a directory and I get results like this in coulmn A.
There will not always be the same number of files, it changes each
time
:\ab1234
:\df1133
:\gh2345
My problem comes in editing the list - I need to first remove all of
the ":\" from each cell (file name), and then I need to get the last
value in the name to sort the files, in this case it would be
4
3
5
and I would want to but those values in column B.
I am currently searching through google but any help would be greatly
appreciated.
Jim