CODEWITHSUNDEEP
excelexcel-formula
Sandip Payra
Sandip Payra

Reputation: 7

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.

Upvotes: 1

Views: 300

Answers (2)

pnuts
pnuts

Reputation: 59475

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

"rs"0"/-"

Upvotes: 0

Jean-François Corbett
Jean-François Corbett

Reputation: 38520

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.

enter image description here

Upvotes: 3

Related Questions