Help with Formula

[DaveM]

Hi all

In Cell A2 I have =IF(V2,MIN(10,V2),0) any number that is 10 or over to be
10
Which returns a number which in this case is the value 1

In Cell B2 I have =IF(A2>=10,MIN(10,A2,0),A2) any number that is 10 will be
returned as 0
Which returns a number which in this case is the value 1

In Cell C2 I have{ =MCONCAT(IF(X$2:X$3096=X2,MOD(D$2$3096,10),""))}Looks
for duplicates in column X and returns values from column D in order from
top to bottom
Which returns 14

In Cell D2 is the pasted values from Column B

My problem is in column V there is also text characters such as F etc, mixed
with the numbers.

so if any cell in column X as a text character in column V my formula brings
back #VALUE!

example 1022F03

14 is correct

if the 4 was a F

I would get #VALUE!

I've been trying to get 1F that is if the 4 was an F.

90% of them are correct, its just those that have text characters, How can I
Mix, Numbers and text characters in the formulas.

Any help appreciated

Thanks in advance

Dave

 

[Joel]

The VBA function VAL is smarter than the EXCEL function VALUE. If you add
this code to the VBA window it will get the digits up to the first non-digit.

1) Right click tab on bottom of worksheet (normally sheet1) and select View
Code.
2) Use Insert Menu - Module
3) Cut and past Function below
4) on worksheet call with =getnumber(A4)

Function getnumber(Target As Range)
getnumber = Val(Target)
End Function