how to sum alpha numeric?

[yogesh aggarwal]

dear friends,

i have one more problem, how to add alpha numeric numbers

like a12
b34
c1l
d57
total 104
creating a function?

[dilipandey]

Hi Yogesh,

If you have your data in column A, use following function to get the numbers out in column B and then you can easily sum them up.

Please Login or Register  to view this content.

Regards,
DILIPandey

<click on below 'star' if this helps>

[NBVC]

Are you sure that works?

Try in B1:

=LOOKUP(9.999999999E+307,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$30))))

copied down.

Then sum column B.

[dilipandey]

Yogesh, I forgot to mention that it is a Array formula and need to be entered using key combination. Ctrl+Alt+Enter

Regards,
DILIPandey

<click on below 'star' if this helps>

[yogesh aggarwal]

thank you nbvc and mr. dilip,

it worked fantastically well counting only numeric value.

[dilipandey]

You are welcome Yogesh.

cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

[NBVC]

Yogesh, I forgot to mention that it is a Array formula and need to be entered using key combination. Ctrl+Alt+Enter

Regards,
DILIPandey

<click on below 'star' if this helps>

Even with CSE confirmation, it yields #VALUE error, because your formula will only replace "a". Substitute is not an "array formula" function on it's own.

[dilipandey]

Oops... yes you are right.
It was somewhere in my mind and I just tried it with only "a34" and it worked... but yes you are right, it will not work for others...

Regards,
DILIPandey

<click on below 'star' if this helps>

[yogesh aggarwal]

well mr. dilip and Nbvc i am looking for a function to be created in vba would you help me to create a function?

[dilipandey]

Hi Yogesh,

For VBA function, see the below link which will help you extract the number and then you can easily obtain their sum.

http://www.ozgrid.com/VBA/ExtractNum.htm

Regards,
DILIPandey

<click on below 'star' if this helps>