Separate numbers from group of numbers

[QuantEdge]

hi,
does anyone know how to slip a group of numbers into digits from left to right... did some googling but can't find a simple solution? sheet below:

[rorya]

Assuming that it's a set number of digits, just use a series of MID formulas:
=MID(A1,1,1)
=MID(A1,2,1)
=MID(A1,3,1)
etc.

[Pete_UK]

Put this formula in J1:

=MID($A1,COLUMNS($J:J),1)

then copy across. You also need to set your Calculation Options (on the Formulas menu) to Automatic.

Hope this helps.

Pete

[alansidman]

If all your entries are the same length, then you could use text to columns, fixed width. This can be found on the data tab on the ribbon.

[QuantEdge]

thx for all these solutions.... first one is the shortest so I will use that one.

very good help all round.)

[QuantEdge]

PS how do I star** you all?

[Pete_UK]

I don't think you can do that in one operation - you have to click on each person's "star" in turn then fill in the dialogue box (if you want to leave a message) and click OK.

Hope this helps.

Pete

[alansidman]

Click on Add Reputation

[avk]

Without formula also you can do this.
>Select the number cells, and click Data > Text to Columns.
> In the step 1 of the Convert Text to Columns Wizard, check Fixed width
> Click Next to go to step 2 of the Wizard, and click at the positions you want to create the break line
> Click Next to go to step 3, and select a destination you want to output the split numbers.
>Click Finish, and then the selected numbers are split into columns.

[QuantEdge]

Pete_UK,
"Calculation Options (on the Formulas menu) to Automatic."

I can't find this option? where is it?

I mean its already set to Automatic, but I can use the slip digit?

[Pete_UK]

When I opened your first file it was set to Manual, but that might have been due to other (earlier) files that I had downloaded.

In this second file you need to do this:

=IF(MID(K1,1,1)="3",1,"")

The second parameter of the MID function is the character number where you should start to take the middle-string, but K1 is only a single character. Also, the MID function returns a text value, so you need to put quotes around the 3 so that you are comparing like with like.

Hope this helps.

Pete

[QuantEdge]

thx Pete, ... all sorted now*) hope to hear from you in the coming posts...

[Pete_UK]

Glad to hear you got it working, and thanks for the rep earlier.

EDIT: … and just now.

Pete