Help with Formula
 

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:D$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

Help with Formula
 

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


"DaveM" wrote:

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:D$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