Split Number To Digits

Can a vba macro be provided for splitting a number into digits? The number will be in Sheet 1 but splitted number will be on sheet 2. Splitting of numbers means a number entered into a cell will be splitted into different column/cells with one digit per cell.

I think this will do what you want.

Please Login or Register  to view this content.

Hi

Another option

Please Login or Register  to view this content.

rylo

Thanks to duo for the solution but how can I modify the code for more than one row involved? secondly, this code has to be run for getting the solution . can the code be made in such a manner that as soon as data in specified cells of sheet1 are entered, number will be splitted automatically?

Hi

Change it to an event macro. Right click on the sheet tab where you are going to enter the number, select view code, and paste the following code.

Please Login or Register  to view this content.

Adjust so that the output sheet is correct.

Now enter a number in column1 of the source sheet, and it should split the output to the output sheet.


rylo

Thanks for the reply But where can I make the chages for start column in output sheet. In output sheet splitting is starting from column A, but if I want the same to start from column D, where I have to make changes?

Secondly, Say in cell A1 of input sheet is of 5 digits and cell A2 of input sheet is of 6 digits, in Output sheet, number alingnment is not correct.ie output is coming as below:

12345 is 1 2 3 4 5
123456 is 1 2 3 4 5 6

whereas I want
12345 is 1 2 3 4 5
123456 is 1 2 3 4 5 6

Thanks for the reply regarding Output display sorry that it did not display correctly. I am attaching a sample WB as display is not proper here.

Someone please have a look into it.

Hi

How are we to know the maximum number of digits that you want to format so it can be right justified? Is 6 the absolute limit for every number??? What happens if a number is input with more than 6 digits?


rylo

Thanks for the reply. Maximun length of the number will be 14 User cannot enter more than 14 digit number in input sheet as validation will restrict them.

HI

See how this goes.

Please Login or Register  to view this content.

rylo

Exactly working as per desired manner. One guidance
1. Just tell me the logic where can I change the column start no. In your code, in output sheet, it is starting from Column N as I told it will be for 14 digits. But in case I need for say 10 digits, 6 digits etc., where shall I make the changes? So that I can use the code in future also with modification.

One problem in the code:
1. In your code, in the input sheet, if the number contains zero, in output sheet, that cell remains blank. say the no. is 1001 in the inout sheet, in the output sheet, the result is 1 <blank> <blank> 1. Why it is not recognizing zeros?

Why it is not recognizing zeros?

A slight mod to Rylo's code changes this:

Please Login or Register  to view this content.

Just tell me the logic where can I change the column start no.

Change the value of constant n.

HI

Yea, didn't consider that.

How about

Please Login or Register  to view this content.

2) Modify the number in the line

Please Login or Register  to view this content.

to determine the number of characters you want to see.


rylo

How about ajusting the first column by the Len of the number.

Please Login or Register  to view this content.

How about ajusting the first column by the Len of the number.

Valid alternative, Mike, if right-justifying the results is not significant.

The first digit is offset (14-Len(holder))+1 columns
The last digit is offset (14-Len(holder))+Len(holder) = 14 columns

That is right justified, as sujittalukder wants.

My apology, Mike, I did not read your code carefully.

No problem. You just incited the explaination that I should have included at first.

Thanks to all for the reply. The code is working perfectly.

Amazing, but can you revise vb to include two decimal places. say the numbers 2550.00 and 550.01 and 50.00
I tried the above but in 2550.00 the digits picked up were 2 5 5 0 but when i tried 550.01 result is 5 5 0 . 0 1
Can I have a fix on this. Thank you