Excel-formula

This query is : Resolved

Share Report Follow Query Ask a Query

RISHABH

RISHABH (Querist)
Follow

11 July 2012 HOW TO ADD
Rs.50 + Rs.65
ie including alphabets

is there any formula TO ADD THIS

A50+A98

CA Siddhartha Bhardwaj

CA Siddhartha Bhardwaj (Expert)
Follow

11 July 2012 Enter numbers without writing 'Rs.'

Right click on the cell, select format cells.
Click on number tab.
Under category select Accounting.
Under symbol, select Rs. English (India)

RISHABH

RISHABH (Querist)
Follow

11 July 2012 IS THERE ANY FORMULA IN EXCEL TO ADD ALPHANUMERIC CHARACTER

eg: A52+A25+A55

Aashok Kumar Sharma

Aashok Kumar Sharma (Expert)
Follow

21 July 2024 In Excel, you cannot directly add alphanumeric characters like "A52", "A25", and "A55" using a simple addition formula because Excel interprets these as text strings rather than numerical values. However, if you want to extract the numerical part from these alphanumeric strings and then perform addition, you can use a combination of functions like `LEFT`, `MID`, `RIGHT`, and `VALUE`.

Assuming your data is in cells A1, A2, and A3 (A1 = "A52", A2 = "A25", A3 = "A55"), you can extract the numeric part using formulas like this:

For A1:
```excel
=VALUE(MID(A1, 2, LEN(A1)-1))
```

For A2:
```excel
=VALUE(MID(A2, 2, LEN(A2)-1))
```

For A3:
```excel
=VALUE(MID(A3, 2, LEN(A3)-1))
```

These formulas extract the numeric part (52, 25, 55) from the alphanumeric strings. Then, you can add them up using a simple addition formula:

```excel
=VALUE(MID(A1, 2, LEN(A1)-1)) + VALUE(MID(A2, 2, LEN(A2)-1)) + VALUE(MID(A3, 2, LEN(A3)-1))
```

This formula will give you the sum of the numeric values extracted from the alphanumeric strings "A52", "A25", and "A55".

### Explanation:
- `MID(A1, 2, LEN(A1)-1)`: This function extracts characters from the middle of a text string (`A1`), starting from the 2nd character (`2`), and for a length equal to the length of `A1` minus 1 (`LEN(A1)-1`). This effectively removes the first character ("A") from the alphanumeric string, leaving the numeric part.
- `VALUE(...)`: Converts the extracted text string into a numerical value that can be used in mathematical operations like addition.

Make sure that your alphanumeric strings are consistently formatted and the numeric parts are always in the same position within each string for these formulas to work correctly. Adjust the formulas as needed based on your actual data structure in Excel.

You need to be the querist or approved CAclub expert to take part in this query .
Click here to login now