Adding numbers enclosed in characters

Question

I am trying to add numbers in excel which contain characters.

For example, I want to add

rs30/-
rs40/-
rs45/- 

I want result as rs115/-

I tried

=SUM(IF(ISNUMBER(--A1:A3),--A1:A3)) 

but that gives zero.

Answer 1

You can use MID to extract the number text from the character string, then VALUE to convert that text to an actual numerical value, then add those using SUM, then use & to concatenate with the characters you want before and after the result.

Example:

="rs" & SUM(VALUE(MID(A1:A3,3,LEN(A1:A3)-4))) & "/-"

entered as an array formula using Ctrl Shift Enter.

Answer 2

You might prefer to strip out rs and /- (with Find & Select), then format as:

"rs"0"/-"