Separating Numbers

[Guest]

Hi:

Suppose I have 16 numbers in a cell (A1), e.g. 1234567890123456. Now I want
to spit the numbers in 4 parts for that I get the numbers 1234 in cell A2,
5678 in cell A3, 9012 in cell A3 and 3456 in cell A4.

Anyone have any idea how I can do this?

Thank you.

Himu.

 

[Peo Sjoblom]

Use mid

=MID(A1,1,4)

then

=MID(A1,5,4)

and so on

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Stan Brown]

Hi:

Suppose I have 16 numbers in a cell (A1), e.g. 1234567890123456. Now I want
to spit the numbers in 4 parts for that I get the numbers 1234 in cell A2,
5678 in cell A3, 9012 in cell A3 and 3456 in cell A4.

Anyone have any idea how I can do this?

A2: = A1/1000000000000
A3: = mod(A1/100000000,10000)
A4: = mod(A1/10000,10000)
A5: = mod(A1,10000)

I'm _assuming_ Excel has enough precision to do this correctly. Test
it on a couple of cases.

 

[Ragdyer]

Check out "Text To Columns".

This will split your data into the columns you wish, without the necessity
of possibly having to eliminate parsing formulas.

 

[Guest]

Peo:

the problem i am facing with using "MID" function is that, I have to specify
the start start_num and num_chars. But I want somthing that will divide the
numbers in the cell in 4 parts without having me to manually type the
start_num and num_chars.

My actual problem is that I have a number with 164 digits in one cell and i
want to divide them into parts of 4 digits in each parts. I just simplied my
question to a 16 digits number. Hope you understand why it is difficult for
me to manually type the start_num and num_chars.


Thankx!

HIMU

 

[Ragdyer]

Text To Columns should work well in such a scenario.

 

[R.VENKATARAMAN]

try this macro and check whether you get what you want
Option Explicit


Public Sub test()
Dim i As Integer
Dim j As Integer
'ActiveCell.FormulaR1C1 = "=LEN(R[-8]C)"
j = Len(Range("c4").Value)
MsgBox j
Dim mystring As String
Dim result As Range
Set result = Range("a10")'****************

mystring = Range("c4").Value

Dim x1 As String, x2 As String, x3 As StdFont, x4 As String
i = 1
line1:
result = Mid(mystring, i, j / 4)

Set result = result.Offset(1, 0)
i = i + j / 4
If i > j Then Exit Sub
GoTo line1
End Sub

if successful change
the line marked ******* to suit you.

 

[Peo Sjoblom]

That's easily fixed

=MID($A$1,ROW(1:1)*4-3,4)

copy down and you'll get it

--
Regards,

Peo Sjoblom

(No private emails please)