CODEWITHSUNDEEP
excel-formula
Anas
Anas

Reputation: 39

How to SUM values in cell with Text Prefix

I have a column in excel with the values with currency prefix like: Rs. 100, rs. 200, rs 125 etc., I want to sum the digits and discard the ("Rs. , rs., rs") text; also how to add "Rs." in resultant SUM automatically?

Upvotes: 0

Views: 2183

Answers (2)

SanV
SanV

Reputation: 945

The formula to extract numbers in your scenario is:
=--RIGHT(A1,LEN(A1)-SEARCH(" ", A1))
Once extracted, you should be able to add them up.
Credit to @PraveenDA for pointing out -- prefix tip to convert text to number. enter image description here

Upvotes: 2

Praveen DA
Praveen DA

Reputation: 354

Try this out..

=SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)) * ROW(INDIRECT("1:"&LEN(A1))), 0), ROW(INDIRECT("1:"&LEN(A1))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A1)))/10)

this formula will pick all the number from cell

Drag the formula in Column B (in my case) and have =sum(B:B) formula at the end

enter image description here

Upvotes: 1

Related Questions