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" <[email protected]> wrote in message
news:[email protected]...
> 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"
<[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>>
>>