Extracting only numerical data from a string

[bsagal]

Hello,

I am using a dlookup function to get a string from a table.

The problem is that the strings are formatted like this:

7Y or 10Y etc.

Basically I want to drop the Y at the end.

Is there a quick way of telling VBA to only read until it reaches a non-numeric character?

Or am i forced to convert them individually (which will be a hastle because some are 1 digit numbers and some are 2)

Thanks

 

[PaulF]

if they all end in "Y" then just use

Left(FieldName,len(FieldName) - 1)

PaulF

 

[bsagal]

Thanks for your reply.

I think for some reason regardless of whether or not it is 7Y or 10Y, the length of the string is constant.

This means that if i use -1 in the argument, it eliminates the Y from 10Y , but not from 7Y or any other single digit number.

For those i would have to use -2.

Is there another way of doing it?

 

[PaulF]

add the Trim function to it

Left(Trim(FieldName),len(Trim(FieldName)) - 1)

PaulF

 

[bsagal]

Thanks very much!

 

[TonyJollans]

Hi bsagal,

You might also consider the Val Function. This takes as many characters from the front of a string as it can interpret as a number so ..

Val(FieldName)

 ' Gives 7 from "7Y" and 10 from "10Y"

Enjoy,
Tony

 

[bsagal]

Thanks very much Tony, i think that is a cleaner method, but they both work!