Adding numbers enclosed in characters

问题:

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.

回答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.

回答2:

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

"rs"0"/-"