FormatProblem

  • Thread starter Thread starter jb
  • Start date Start date
J

jb

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
 
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.
 
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)
 
Back
Top