Val Function

[skittlz]

Hi Everyone,

Very useful post. I'm having a few issues with the val function, and I'm hoping one of you guys can help. As you said, val function does not recognize spaces between numbers. A piece of my code is currently set up to find a value of a cell. However, the cell contains additional numbers which I do not want - for example, a cell contains "156 2 mm spaces" - I just want the val function to give me the quantity (156). I want the val function to recognize space as a non-numeric character.

Please help. Thanks!

[Rick Rothstein]

Hi Everyone,

Very useful post. I'm having a few issues with the val function, and I'm hoping one of you guys can help. As you said, val function does not recognize spaces between numbers. A piece of my code is currently set up to find a value of a cell. However, the cell contains additional numbers which I do not want - for example, a cell contains "156 2 mm spaces" - I just want the val function to give me the quantity (156). I want the val function to recognize space as a non-numeric character.

Simple... just replace the space with a non-digit inside the Val function. For example...

QuantityText = "156 2 mm spaces"
Quantity = Val(Replace(QuantityText, " ", "X")

[mrmmickle1]

Val("1234.5.56Excel")

I am new to vba and i have been looking at some string items today. Regarding the above code I have a question.... Why does this code only return:

1234.5?

If this is to return numbers why would it not return

1234.5.56?

Is it because the 56Excel is alphanumeric? Does Val() not recognize numbers that are alphanumeric?

[Rick Rothstein]

Val("1234.5.56Excel")

I am new to vba and i have been looking at some string items today. Regarding the above code I have a question.... Why does this code only return:

1234.5?

If this is to return numbers why would it not return

1234.5.56?

Is it because the 56Excel is alphanumeric? Does Val() not recognize numbers that are alphanumeric?

Val returns the leading number from a text string... it stops looking at the first character that no longer can be used to form a real number. Since numbers can only have one decimal point, it rejects the second decimal point because it cannot be used to form a real number, so Val returns everything before that invalid numeric character.

[mrmmickle1]

Rick,

This makes sense. Now that I understand this I will continue on to lower responses. I just wanted to make sure that I was fully grasping the concept before I moved on to something else. I was looking at this website a little earlier in the day: VBA Excel: Strings So when I came across this thread it was good reinforcement with additional knowledge! Thanks again for explaining.