Finding last number in cell

[Guest]

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

 

[Jake Marx]

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]

 

[Guest]

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]