Val function not enough to solve this (1 Viewer)

Hi,

I have data coming via Excel spreadsheet from an external source and it includes a column called PercentRecruitment. It looks like a percentage but actually it seems to be text. Some sample values are

10%
15.5%
0%
121.66666667%


There's no consistency with the number of decimal places.

I tried IsNumeric and they all returned 0

I want to use a query to convert these to "real" percentages without any decimal places, or at least to some other numeric format that I can deal with, but VAL() does not work if there are decimal places. So for the data above I get

10
#Error
0
#Error

Is there another function I can use instead of (or in addition to) Val() ?

Thanks

You need to strip the percent sign off of those then do your Val():

PercentValue: Val(Mid([YourFieldNameHere],1,Len([YourFieldNameHere])-1))

Thank you so much for that. I looked for help on this earlier and the Microsoft site said that Val(" 1615 198th Street N.E.") returns 1615198 i.e. everything but the digits. So I'm puzzled why it did not remove the decimal point and the % symbol.

But your suggestion works! I then divided the whole thing by 100, expressed the result as % and I have exactly what I want.

Thank you!!