Adding Special Numbers in MS Excel

An unusual question in MS Excel:
I’m working on a specific problem in MS Excel and I’ve to enter the numerical data in the following format 3892 (1), 2652 (2), etc. Is it possible to add the numbers outside the parenthesis only?
If the user entered only specific number of like 1234 (1), 5678 (2) then we could use the LEFT function, extract the first 4 digits and then add them either directly or by first multiplying the extracted 4 digits and then multiplying them with 1 to convert them into numerical values.Now what would happen if the user entered numbers like 123 (1), 6789 (2)? Since the number of digits are 3 and 4 respectively we would have to apply the LEFT function separately on each of the values. Also notice that the data entered by the user in such a format is automatically treated as text by Microsoft Excel. The left alignment of the data in the cells is an indication. Numerical values are always right aligned in an Excel worksheet cell. Therefore we need to adopt a different strategy. Using the SEARCH function we can identify the location of the space in entries like 1234 (1) or 123 (2). Once that has been accomplished we can use the LEFT function in combination with the SEARCH function to extract only the required digits. Our custom function now looks like this: =LEFT (A2, SEARCH (” “, A2, 1)) A2 is the cell address of the cell that contains the data. We call this kind of function a nested function. Before the LEFT function can complete its work, the SEARCH function finds the space between the digits and the digit(s) in parenthesis or brackets. Only then the LEFT function completes its job of extracting the numbers from the left of the data up to the space. In this manner the correct number of digits are extracted. Now we add the extracted digits using standard arithmetic operations like ‘+’and Excel takes care of the rest.

Screenshot: Adding special numbers using the search function nested in the left function

Screenshot: Adding special numbers using the search function nested in the left function

A user may also consider creating a macro using Excel VBA to automate the complete process.

Watch the training video below to learn how the complete process of creating the formula and its implementation is carried out step by step.

Leave a Reply

Your email address will not be published. Required fields are marked *