SUM with partial cell content in a range

[raheem]

I need to SUM a range where some of the cells contain partial text as below:

5
6
2
L5
4

SUM should be 22 taking the digit from second last cell also.

 

[James006]

Hello,

There are several solutions to Sum from Text ...

Could you provide several examples of these ' special ' cells ...

 

[ravisingh]

Hi Raheem, You might look at this:
https://www.mrexcel.com/forum/excel-questions/443983-extract-only-numbers-text-string.html

 

[raheem]

In the particular sheet, the data is exactly same. Number will always be a single digit and if there is an alphabet, it will always be one alphabet with one digit.

 

[raheem]

Hello,

There are several solutions to Sum from Text ...

Could you provide several examples of these ' special ' cells ...

In the particular sheet, the data is exactly same. Number will always be a single digit and if there is an alphabet, it will always be one alphabet with one digit.

​

 

[James006]

Hello,

You can use following Array Formula

=SUM(RIGHT(A1:A5,1)*1)

Hope this will help

 

[Michael M]

If there is more than 1 alpha character, you could use

=RIGHT(A1,SUM(LEN(A1) - LEN(SUBSTITUTE(A1, {"0","1","2","3","4","5","6","7","8","9"},""))))*1

Drag down and then autosum at the bottom

 

[raheem]

Hello,

You can use following Array Formula

=SUM(RIGHT(A1:A5,1)*1)

Hope this will help

Thanks. This worked

 

[raheem]

If there is more than 1 alpha character, you could use

=RIGHT(A1,SUM(LEN(A1) - LEN(SUBSTITUTE(A1, {"0","1","2","3","4","5","6","7","8","9"},""))))*1

Drag down and then autosum at the bottom

Thanks. Will save for future (having more than 1 alpha)

 

[James006]

Thanks. This worked

You are welcome ...