Finding last number in cell
 

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

Finding last number in cell
 

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

Finding last number in cell
 

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