I have 2 situations that need to be build in an existing spreadsheet. Here
is what I have:
B C D E F G H I J K L M N
9 0 SPACE 5 0 0 0 4 2 1 =+b =+C =mod(a formula)

Result:
B C D E F G H I J K L M N
9 0 5 0 0 0 4 2 1 9 0 2

I need the values of E through N to be in one cell.

I also need to be able to do the reverse:
If I put a 10 digit number in one cell, I need to be able to put each number
in a separate cell, all in the same spreadsheet.

Any sugestions would be helpful. I have tried to concatenate; however, I
think it does not work because it is for text only. I am unable to reveal
the mod check formula, so even if I can get the first 9 numbers in the field
and have to manually change the number, that would work also.

Thanks in advance for any help.

To join the values into a single numeric value, use:
=VALUE(E1&F1&G1&H1&I1&J1&K1&L1&M1&N1)

To break the number into individual digits, use:
=VALUE(MID($A$1,1,1))
=VALUE(MID($A$1,2,1))
=VALUE(MID($A$1,3,1))
:
:
=VALUE(MID($A$1,101))

HTH

Alan P.

"Jeanne" wrote in message
...
I have 2 situations that need to be build in an existing spreadsheet. Here
is what I have:
B C D E F G H I J K L M N
9 0 SPACE 5 0 0 0 4 2 1 =+b =+C =mod(a formula)

Result:
B C D E F G H I J K L M N
9 0 5 0 0 0 4 2 1 9 0 2

I need the values of E through N to be in one cell.

I also need to be able to do the reverse:
If I put a 10 digit number in one cell, I need to be able to put each
number
in a separate cell, all in the same spreadsheet.

Any sugestions would be helpful. I have tried to concatenate; however, I
think it does not work because it is for text only. I am unable to reveal
the mod check formula, so even if I can get the first 9 numbers in the
field
and have to manually change the number, that would work also.

Thanks in advance for any help.

To combine your numbers, try this:
=--(E1&F1&G1&H1&I1&J1&K1&L1&M1&N1)

To separate your numbers, try this:
=--MID($N$1,COLUMN(B1),1)
Click the fill handle and drag *across* your 10 columns.
THEN, click the fill handle of the 10 column *selection*, and drag down as
needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Jeanne" wrote in message
...
I have 2 situations that need to be build in an existing spreadsheet.
Here
is what I have:
B C D E F G H I J K L M N
9 0 SPACE 5 0 0 0 4 2 1 =+b =+C =mod(a formula)

Result:
B C D E F G H I J K L M N
9 0 5 0 0 0 4 2 1 9 0 2

I need the values of E through N to be in one cell.

I also need to be able to do the reverse:
If I put a 10 digit number in one cell, I need to be able to put each
number
in a separate cell, all in the same spreadsheet.

Any sugestions would be helpful. I have tried to concatenate; however, I
think it does not work because it is for text only. I am unable to reveal
the mod check formula, so even if I can get the first 9 numbers in the
field
and have to manually change the number, that would work also.

Thanks in advance for any help.

Made a typo in the separation formula.
Should start with column A1:
=--MID($N$1,COLUMN(A1),1)

Also, I assumed that you wanted to go across the columns, but in re-reading
your post, you only mentioned "cell".

To separate the numbers into vertical rows, use this:
=--MID($N$1,ROW(A1),1)
And drag down to copy.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Ragdyer" wrote in message
...
To combine your numbers, try this:
=--(E1&F1&G1&H1&I1&J1&K1&L1&M1&N1)

To separate your numbers, try this:
=--MID($N$1,COLUMN(B1),1)
Click the fill handle and drag *across* your 10 columns.
THEN, click the fill handle of the 10 column *selection*, and drag down as
needed.
--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-


"Jeanne" wrote in message
...
I have 2 situations that need to be build in an existing spreadsheet.
Here
is what I have:
B C D E F G H I J K L M N
9 0 SPACE 5 0 0 0 4 2 1 =+b =+C =mod(a formula)

Result:
B C D E F G H I J K L M N
9 0 5 0 0 0 4 2 1 9 0 2

I need the values of E through N to be in one cell.

I also need to be able to do the reverse:
If I put a 10 digit number in one cell, I need to be able to put each
number
in a separate cell, all in the same spreadsheet.

Any sugestions would be helpful. I have tried to concatenate; however,
I
think it does not work because it is for text only. I am unable to
reveal
the mod check formula, so even if I can get the first 9 numbers in the
field
and have to manually change the number, that would work also.

Thanks in advance for any help.

Alan, thanks for your response. When I try this I get #VALUE!



"Alan Perkins" wrote:

To join the values into a single numeric value, use:
=VALUE(E1&F1&G1&H1&I1&J1&K1&L1&M1&N1)

To break the number into individual digits, use:
=VALUE(MID($A$1,1,1))
=VALUE(MID($A$1,2,1))
=VALUE(MID($A$1,3,1))
:
:
=VALUE(MID($A$1,101))

HTH

Alan P.

"Jeanne" wrote in message
...
I have 2 situations that need to be build in an existing spreadsheet. Here
is what I have:
B C D E F G H I J K L M N
9 0 SPACE 5 0 0 0 4 2 1 =+b =+C =mod(a formula)

Result:
B C D E F G H I J K L M N
9 0 5 0 0 0 4 2 1 9 0 2

I need the values of E through N to be in one cell.

I also need to be able to do the reverse:
If I put a 10 digit number in one cell, I need to be able to put each
number
in a separate cell, all in the same spreadsheet.

Any sugestions would be helpful. I have tried to concatenate; however, I
think it does not work because it is for text only. I am unable to reveal
the mod check formula, so even if I can get the first 9 numbers in the
field
and have to manually change the number, that would work also.

Thanks in advance for any help.

Jeanne,

Which bit doesn't work? The only reason that I can think of for that error
is a non-numeric chatacter in the cell. Perhaps a sample of the data that
you're using would help.

Alan P.

"Jeanne" wrote in message
...
Alan, thanks for your response. When I try this I get #VALUE!



"Alan Perkins" wrote:

To join the values into a single numeric value, use:
=VALUE(E1&F1&G1&H1&I1&J1&K1&L1&M1&N1)

To break the number into individual digits, use:
=VALUE(MID($A$1,1,1))
=VALUE(MID($A$1,2,1))
=VALUE(MID($A$1,3,1))
:
:
=VALUE(MID($A$1,101))

HTH

Alan P.

"Jeanne" wrote in message
...
I have 2 situations that need to be build in an existing spreadsheet.
Here
is what I have:
B C D E F G H I J K L M N
9 0 SPACE 5 0 0 0 4 2 1 =+b =+C =mod(a formula)

Result:
B C D E F G H I J K L M N
9 0 5 0 0 0 4 2 1 9 0 2

I need the values of E through N to be in one cell.

I also need to be able to do the reverse:
If I put a 10 digit number in one cell, I need to be able to put each
number
in a separate cell, all in the same spreadsheet.

Any sugestions would be helpful. I have tried to concatenate; however,
I
think it does not work because it is for text only. I am unable to
reveal
the mod check formula, so even if I can get the first 9 numbers in the
field
and have to manually change the number, that would work also.

Thanks in advance for any help.