# Alphanumerals

#### marcus.brown

##### Board Regular
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!

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### twistinginthewind

##### Board Regular
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

##### Well-known Member
you can try something like this

Code:
``````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
ABCD
11.5Fr2Ger5.70
2.5Sp3Fr
3.2Fr1Sp
41Fr.5Ger
Sheet1

#### pgc01

##### MrExcel MVP
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

Last edited:

#### Scott Huish

##### MrExcel MVP
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.

Replies
4
Views
415
Replies
8
Views
321
Replies
3
Views
441
Replies
5
Views
294
Replies
4
Views
448

1,190,648
Messages
5,982,118
Members
439,756
Latest member
alice128

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back