Split of digits in excel


I need help how to split digits (units, tens & hundreds in one coloumn, & thousands, ten thousands and above in another coloumn) please guide me how to split.



Selected Answer

This formula returns the thousands and above from a number in cell A1.


You can achieve the same result by simply formatting the cell as Custom #, (one number symbol followed by your thousands separator, in English it's a comma). Compared to the formula, this method just changes the display, not the value of the cell.

The next formula extracts and displays the units, tens and hundreds.


Hello, Mr. Shekar,

You can copy (from C5) less than thousands in another cell with:


and for more than that,


But if you want to split '9' from 8976 (and so on), please click here.

Thank you!



Good morning Cchabi, your second formula should perhaps look like this, =IF(LEN(C5)>3,LEFT(C5,LEN(C5)-3),""). However, bear in mind that this is treating the number in C5 as text and that Excel's LEFT() function returns a text string as well. Therefore the ability to use the results in straight forward calculations will be inhibited.
Variatus (rep: 4148) Apr 26, '18 at 9:28 pm
Good morning, sir! Nice to see you online.
Actually, my purpose here is not extracting '67' from 67254 (and so on) for which your answer has addressed clearly. The formula above just picks a data if it is less than thousand or more than hundred, without splitting it. (I might not have understood the question clearly. I got confused how to keep units (8), tens(7) and hundreds(6) in the same column from 54678.)

Thanks for your continuous guidance!
Chhabi Acharya (rep: 111) Apr 26, '18 at 9:50 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login