Alphanumerals

[marcus.brown]

Hi Guys,

I'm new here, so could do with some help! I need to create an alphanumeric function. The fuction needs to have a range and a criteria. This function will then some the numbers in the range where the text in each of the cells match the criteria.

e.g. alphanumerals(a2:b4, "Fr") would sum all the all numbers which end with the letters Fr (not case sensitive).
a b
1 1.5Fr 2Ger
2 0.5Sp 3Fr
3 0.2Fr 1Sp
4 1Fr 0.5Ger

In this case, I should get a total of 5.7

The formal for each of the cells with the alphanumerals would be <number><charachters>

The function would sum number, where criteria matches characters.

Thanks all!

 

[twistinginthewind]

function Alpha_Num(rng as range,cnd as string)
tot=0
for each c in rng.cells
if instr(lcase(c.value),lace(cnd))>0 then
tot=tot+val(c.value)
end if
next
Alpha_Num=tot
End function

 

[TheNoocH]

you can try something like this

Function SumText(rng As Range, myInput As String) As Double
         SumText = 0
   For Each cell In rng
      If Right(cell, Len(myInput)) = myInput Then
         SumText = SumText + Left(cell, Len(cell) - Len(myInput))
      End If
   Next cell
End Function

Book1
	A	B	C	D
1	1.5Fr	2Ger	5.70
2	.5Sp	3Fr
3	.2Fr	1Sp
4	1Fr	.5Ger
Sheet1

 

[pgc01]

Hi Markus
Welcome to the board

Assuming you write in C1 the string with the numbers you want to sum ("Fr", Ger", "Sp")

try:

=SUM(IF(RIGHT(A1:B4,LEN(C1))=C1,--LEFT(A1:B4,LEN(A1:B4)-LEN(C1))))

confirm with CTRL+SHIFT+ENTER

 

[Scott Huish]

Perhaps, with the criteria in C1:

=SUM(IF(ISNUMBER(SUBSTITUTE(A1:B4,C1,"")+0),SUBSTITUTE(A1:B4,C1,"")+0))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.