When calculating in Excel, the returned result will usually include an integer part and a decimal part separated by commas. So how to separate the number before and after the comma. Please follow the instructions how to split numbers first and after comma in Excel are presented by tip.com.vn in the article below.
I. Get the number before the comma.
1. Use the TRUNC function.
To separate numbers before commas in Excel just use the TRUNC function. The TRUNC function will help you remove the number after the comma, to do this, apply the following formula:
- Number: original number before splitting.
- Num_digits: A number that specifies the precision of the truncation. The default value of num_digits is 0 (zero).
For example: =TRUNC(A2) will return -12 and =TRUNC(A3) will return 12 as shown below
2. Use the INT function.
The INT function will round down to the nearest integer based on the value of the fractional part of a number so we can also use the INT function to separate the part before the decimal point.
The example below uses the INT function and you will see the result is different from the TRUNC function above
II. How to get numbers after commas in excel.
There is no specific function to get numbers after commas in Excel that we have to use trick. Subtract the original number from the result of the TRUNC function that you calculated, we will get the number after the comma.
To make the step faster, you can apply the following formula:
= number to split – TRUNC(number to be used)
If you want the return result to be a positive number, you need to use the ABS function.
=ABS(base number – TRUNC (base number)).
How to separate the number before and after the comma in Excel It’s simple, right. Hope the article is useful to you. Good luck.