Splitting Numbers in a Column

  • Thread starter Thread starter jer101
  • Start date Start date
J

jer101

:confused: I have a column of numbers that are 21 digits long. For
each number the first 7 digits represent a department,
the next 5 digits represent a class and the last 9 digits
represent the account #. Is there a simple way to
separate the single column of numbers into 3 columns
with the appropriate headings?

An example of the type of numbers in the column is as follows:

100310310000513000000
105210010000521000000
101900020000521000000
105400010000521000000
105900020000723000000
104600010000522000000
101710010000524000000
102600020000521000000
10262212000052100000
 
jer,

For the department column:
=mid(A2, 1, 7)

For the class:
=mid(A2,8, 5)

Account:
=mid(A2,13, 9)

Copy down with fill handle. To permanantly set them, copy the columns with
the formulas, then with the same selection, Edit - Paste special - Values.
Be sure the selection for paste is identical to that of the copy. Now you
don't need the original cells.
 
Have you checked that the data is correct? I notice that the last 6 digits of
all of your numbers are 0's. Should that be? If not, it's because you typed in
the numbers, and since Excel's limit is 15 digits, the remainder are converted
to 0's.

I would check the data before going any farther with this.
 
Back
Top