Separating Numbers
 

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.

Use mid

=MID(A1,1,4)

then

=MID(A1,5,4)

and so on

--
Regards,

Peo Sjoblom

(No private emails please)


"Himu" wrote in message
...
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.

On Wed, 1 Jun 2005 19:50:01 -0700, "Himu"
wrote:

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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"I feel a wave of morning sickness coming on, and I want to
be standing on your mother's grave when it hits."

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Himu" wrote in message
...
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:

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

"Peo Sjoblom" wrote:

Use mid

=MID(A1,1,4)

then

=MID(A1,5,4)

and so on

--
Regards,

Peo Sjoblom

(No private emails please)


"Himu" wrote in message
...
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.

Text To Columns should work well in such a scenario.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Himu" wrote in message
...
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

"Peo Sjoblom" wrote:

Use mid

=MID(A1,1,4)

then

=MID(A1,5,4)

and so on

--
Regards,

Peo Sjoblom

(No private emails please)


"Himu" wrote in message
...
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.

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.



Himu wrote in message
...
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

"Peo Sjoblom" wrote:

Use mid

=MID(A1,1,4)

then

=MID(A1,5,4)

and so on

--
Regards,

Peo Sjoblom

(No private emails please)


"Himu" wrote in message
...
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.

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)


"Himu" wrote in message
...
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

"Peo Sjoblom" wrote:

Use mid

=MID(A1,1,4)

then

=MID(A1,5,4)

and so on

--
Regards,

Peo Sjoblom

(No private emails please)


"Himu" wrote in message
...
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.