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.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Split of digits in excel
0
Answers
0
Selected Answer
This formula returns the thousands and above from a number in cell A1.
=INT($A1/1000)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.
=MOD($A1,INT($A1/1000)*1000)
0
Hello, Mr. Shekar,
You can copy (from C5) less than thousands in another cell with:
=IF(LEN(C5)<4,C5,"")and for more than that,
=IF(LEN(C5)>3,C5,"")But if you want to split '9' from 8976 (and so on), please click here.
Thank you!
Discussion
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: 4889) 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!
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