FormatProblem
 

I am using Excel 97. In Column A I have numbers 1234432156788765 that
I have been using the following code to copy to Column B and put a space
between each 4th number, 1234 4321 5678 8765.

=MID(A2,1,4)&" "&MID(A2,5,4)&" "&MID(A2,9,4)&" "&MID(A2,13,4)

For some reason when I try to use this now all I get in cell B2 is the
code, =MID(A2,1,4)&" "&MID(A2,5,4)&" "&MID(A2,9,4)&" "&MID(A2,13,4). It
doesn' t copy the numbers over. I tried to set the numbers in column A
to text and general numbers, but it still would not copy. Any help is
appreciated.

TIA

FormatProblem
 

I entered you number with a leading single quote

'1234432156788765

And your formula worked fine for me. You can't store a number with 16
digits - 15 is the max, so you have to store it as text. You can't make it
text after you enter it as a number by changing the formatting. To be
absolutely sure it is interpreted as text, use the leading single quote.

--
Regards,
Tom Ogilvy


"jb" wrote in message
...
I am using Excel 97. In Column A I have numbers 1234432156788765 that
I have been using the following code to copy to Column B and put a space
between each 4th number, 1234 4321 5678 8765.

=MID(A2,1,4)&" "&MID(A2,5,4)&" "&MID(A2,9,4)&" "&MID(A2,13,4)

For some reason when I try to use this now all I get in cell B2 is the
code, =MID(A2,1,4)&" "&MID(A2,5,4)&" "&MID(A2,9,4)&" "&MID(A2,13,4). It
doesn' t copy the numbers over. I tried to set the numbers in column A
to text and general numbers, but it still would not copy. Any help is
appreciated.

TIA

FormatProblem
 

Tom:

Is there a way to do this with VBA, I have several thousand numbers?

TIA

Tom Ogilvy wrote:
I entered you number with a leading single quote

'1234432156788765

And your formula worked fine for me. You can't store a number with 16
digits - 15 is the max, so you have to store it as text. You can't make it
text after you enter it as a number by changing the formatting. To be
absolutely sure it is interpreted as text, use the leading single quote.

FormatProblem
 

Range("A1").Value = "'" & "1234432156788765"

or if you mean splitting it apart:

myVal= "1234432156788765"
or MyVal = Range("A2").Text

Range("A4") = _
MID(myVal,1,4) & " " & MID(myval,5,4) & " " & _
MID(myVal,9,4) & " " & MID(MyVal,13,4)

--
Regards,
Tom Ogilvy


"jb" wrote in message
...
Tom:

Is there a way to do this with VBA, I have several thousand numbers?

TIA

Tom Ogilvy wrote:
I entered you number with a leading single quote

'1234432156788765

And your formula worked fine for me. You can't store a number with 16
digits - 15 is the max, so you have to store it as text. You can't make
it
text after you enter it as a number by changing the formatting. To be
absolutely sure it is interpreted as text, use the leading single quote.