Excluding non numerical values from a cell containing numerics and letters.

[Wulluby]

Hi,


I am trying to incorporate a line into a macro, the line should pull info from a cell which contains a file name. The filename will always be numbers.extension so for example 12345.txt, I have to pull the numbers only.


I can think of 2 ways but wondering if there’s a ‘neater’ way to do this. One was to find replace on the column itself. Find all .txt and replace with nothing. Another was to figure out how to use the Left function in the code,

strVARI = Worksheets("This Sheet").Cells(r, 2).Value


If I try to put LEFT function into that I imagine it Cells(Left(r, 2, 5)).Value but that’s to many arguments. I’m also worried that if the amount of numbers in the file name changes then the macro will not work.


Is there a simple way I can just get that line to exclude anything in the cell that is not a number?


Thanks in advance.

 

[Luke M]

The Left method needs to be the outside operation. It's also part of the WorksheetFunctions group, so it would be like this:

[pre]

strVARI = WorksheetFunction.Left(Worksheets("This Sheet").Cells(r,2).Value,5)
But, to answer your latter question and in the hope of making the code mode robust, check out this UDF:

http://www.ozgrid.com/VBA/ExtractNum.htm

Just copy the UDF to a regular module, and in your regular code you would have:

strVARI = ExtractNumber(Worksheets("This Sheet").Cells(r,2))

[/pre]

 

[Wulluby]

Thanks on both of those pointers Luke