Splitting Numbers in a Column

: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

--
jer10
-----------------------------------------------------------------------
jer101's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1076
View this thread: http://www.excelforum.com/showthread.php?threadid=26625

0
10/4/2004 9:30:17 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
307 Views

Similar Articles

[PageSpeed] 28

Hi
use Data - Text to Columns' and choose a fixed lenght

-- 
Regards
Frank Kabel
Frankfurt, Germany


jer101 wrote:
>> 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
> 102622120000521000000
0
frank.kabel (11126)
10/4/2004 9:45:53 PM
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.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"jer101" <jer101.1dmfwm@excelforum-nospam.com> wrote in message
news:jer101.1dmfwm@excelforum-nospam.com...
>
> :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
> 102622120000521000000
>
>
> -- 
> jer101
> ------------------------------------------------------------------------
> jer101's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=10760
> View this thread: http://www.excelforum.com/showthread.php?threadid=266250
>


0
nothanks4548 (968)
10/4/2004 9:52:04 PM
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.

>jer101 wrote:
>>> 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
>> 102622120000521000000

0
anonymous (74722)
10/4/2004 9:53:31 PM
Reply:

Similar Artilces:

Split tasks a splitting headache
Inherited another Project IMS. I am noticing right off that the previous scheduler has been starting tasks without their FS preds completing. Yeah, it does show up as a spit bar in the gantt chart. But, does anyone know a way to filter out just task that started before predecessor completion? I'm dealing with several thousand lines. Thanks. -- trailerpup ------------------------------------------------------------------------ trailerpup's Profile: http://forums.techarena.in/members/116596.htm View this thread: http://forums.techarena.in/microsoft-project/1290853.htm ...

How to randomly split a whole dataset into two sub-dataset?
Hi, At your possible convenience, might anyone please kindly answer my question? Thank you very much. How to "RANDOMLY" split the whole data set (n=2000) into two sub dataset (n=1000; n=1000) in SPSS or Excel? Thank you very much. Please take care Caroline zencaroline <zencaroline@gmail.com> writes: > How to "RANDOMLY" split the whole data set (n=2000) into two sub > dataset (n=1000; n=1000) in SPSS or Excel? Create a new variable whose value is randomly distributed. Sort the data on this variable. Take the first 1000 cases ...

sumproduct with two criteria on one column??
I have Window Vista, Office, Excel Col A contains Mon or Tue or Wed or etc days of the weel, Col B contains the date Jan 1, Jan 2, etc, Col C contains a dollar amount for each day. I want to count how many Tue, Wed, Thu, etc there are that have dollar amounts not 0 or blank cells. Each cell in Col C has a formula that calculates the dollar amount from another page of the work book. I cannot get sumproduct to calculate this with two criteria on Col C, if the cell is <0 not to coune it and to sum the cells with >0 amounts. Any thoughts. Thank you, Jerry =SUMPRODUCT(--(A1:A100="...

Report Ignoring negative numbers
I have two columns. One is Company name and the other is positive and negative numbers. I need to group by company name. I also need to group by the pos/neg numbers (ignoring the neg sign) and excluding orphans. I have tried using the ABS, but it changes the neg to a positive. How can I get it to ignore, but still display the neg number. i.e. $134.00 -$134.00 $134.00 $122.14 $122.14 -$122.14 If I had only one $155.00 or -$164.00, etc. I do not want it to show up on my report. Display your number field as is but group on a copy of the f...

numbering filtered rows
If I put = Row() in a column of cells I get the row number. Now, suppose I filter the data. I want to show the sequence row number of the filtered data so that it shows 1,2,3,4,5 etc regardless of the actual row number. For example, I amy have "Joe Smith" on row #45 but when I filter on a particular parameter, "Joe Smith" may be on row 2 of the filtered data. That row is what I need to show. How can I do that? Is it possible without numbering by hand? Glen If you can find a column that always has data in it (like that name column), you could use: in A2 (with headers...

Separating left and right columns of text evenly
I'd like to use Publisher to re-do a menu. How do I keep the menu prices in an evenly spaced column to the right side of the text box? It would be similar to creating a table of contents, perhaps? -- Bill a table would probably be the easiest -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Retired Chief" <RetiredChief@discussions.microsoft.com> wrote in message news:79B75A97-4ADD-48E9-A81D-DF527ECE922A@microsoft.com... | I'd like to use Publisher to re-do a menu. How do I keep the menu prices in | an evenly spaced column to the right side of th...

Numbering a spreadsheet
Hello Excel Experts! I have a spreadsheet that is numbered 1-105, but because of column headers the numbers in my spreadsheet do not coincide with the excel row numbers. Oftentimes, I have to delete rows, but I want the original numbering to stay intact. For example, today, if I delete the row I've numbered as 35, I have to go through and re-number rows 36-105. Is there anyway to avoid manually re-entering the information so that it automatically corrects itself when a row is deleted. Look here: http://j-walk.com/ss/excel/usertips/tip035.htm -- Greeting from the Gulf Coast! http://my...

Can't find my Front End Folder after database split
I just split my database base to allow for ease of use for multiple users. Ererything, it seemed, went smoothly until i try to find the fron-end folder. It is nowhere to be found. Should it be label ****_fe.mbd like the back end? Please help. Thaanks. It will be called that, unless you typed something else in the save as dialog. I thought it ended up in the same folder as the backend after splitting. Easiest way is to go back to the original and split it again, this time take note of where you save it before you press the button. Jeanette Cunningham "Joe" <Joe@discussi...

Math functions on non number data?
is there a way to convert data that is in fraction format to number format to be able to perform math functions without changing the original data type? For instance, change 23 1/2 * 34 3/4 to 23.5*34.75 to come up with 816.625. The result does not need to be in fractional format. thanks for any assistance, Dave DDP I presume you have all of that multiplication in an single cell (otherwise you would simply be able to multiply whatever two cells contain them)? If so, try this formula... =LEFT(A1,FIND("*",A1)-1)*MID(A1,FIND("*",A1)+1,99) -- Rick (MVP - Excel) &qu...

Unwanted replicated split screen
I know I could re-install excell but before I do, is there anyone here who can help me disable a setting that I have no idea how it came about. I can only describe it as 2 identical execl grids on the same worksheet. Accompanying image will show you. Help. Thanks. +-------------------------------------------------------------------+ |Filename: excel_display_duplication.GIF | |Download: http://www.excelforum.com/attachment.php?postid=3855 | +-------------------------------------------------------------------+ -- BoyLeroy -----------------------------------...

Column Headings
Firstly, Happy New Year to you all. In my Outlook 2003 IN BOX I have lost the FROM column. Can anybody tell me how to get it back? Cheers Greg Hi, Greg; Right-click any of the remaining column headers, select "field chooser," find the "from" field, and drag it to the row of column headers. And a Happy New Year to you, IanRoy "Greg" wrote: > Firstly, Happy New Year to you all. > > In my Outlook 2003 IN BOX I have lost the FROM column. > > Can anybody tell me how to get it back? > > Cheers > Greg > > > ...

Cannot insert Column
When I try to insert a column, the option is grayed out - it won't let me. Is this an issue with security that I may have inadvertantly changed? I made no conscious effort to do this, and can't fix. The worksheet is my own. thank.s Do you have your worksheet protected? "Buss" wrote: > When I try to insert a column, the option is grayed out - it won't let me. > > Is this an issue with security that I may have inadvertantly changed? I > made no conscious effort to do this, and can't fix. The worksheet is my own. > > thank.s ...

unique with 2 columns
I have 2 columns with duplicates in the first column and no duplicates in the second. I neeed to only see one value for each in the left and only one of the accounts from the right. ie: abc 1234 abc 12345 bcd 251 eft 600 eft 607 I would like to see either of the abc with the 1234 or 12345 I would like to see the bcd since it's individual with the 251 I would like to see either of the eft with the 600 or the 607 Use a totals query and one of the aggregate functions (First, Last, Min, or Max) on the...

sum column with 2 if criteria
=SUMPRODUCT(--('Working Copy'!A3:A1038="WCenterA"),--('Working Copy'!J3:J1038="1"),'Working Copy'!K3:K1038) this is the formula I entered as an array and I get an N/A#. I'm trying to look match 2 criteria and then add the remaining cells in column K. Instead of "WCenterA" I'd like to use a cell reference. I figured a combination of If and SumIF formulas, but couldn't get that to work either! Help!!! Barbara SUMPRODUCT is not an array function. It sounds like you have an #N/A in of 'Working Copy'!K3:K1038. &...

Display Data On Split Form
Not sure how to ask this but here goes. In AC 2007 I have a split form that I want to display a value that will show regardless of the row I select in the datasheet. This is the number of vacation days one will have through-out the year and of course it changes as vacations days get used up. Any help will be appreciated, James "JamesJ" <jjy@darwin_roadrunner.com> wrote in message news:6A4DFFD9-3302-4A10-B51A-153373D266FD@microsoft.com... > Not sure how to ask this but here goes. > In AC 2007 I have a split form that I want to display a value > that will show regardl...

how do i change hours and minutes into a decimal number? #2
i am trying to turn total flight hrs into a decimal number( ie: 2045hrs 10mins into 2045.10). can anyone help me with a formula which may help in this? time is given HH:MM but i need it turned to HH.M. Hi, The decimal time for 2045h 10m is obtained by multiplying the time by 24 and formatting as general or a number but this will give an answer of 2045.167 and not 2045.1 because .1 of and hour is 6 minutes not 10. Mike "Mills00" wrote: > i am trying to turn total flight hrs into a decimal number( ie: 2045hrs > 10mins into 2045.10). can anyone help me with a formula w...

Number of categories between tick-marks
How do you change the "number of categories between tick-marks labels" setting under Scale of Category (x) axis scale from code? Thanks, Clint Hi, The macro recorder generated this. With ActiveChart.Axes(xlCategory) .TickLabelSpacing = 1 End With Cheers Andy cherman wrote: > How do you change the "number of categories between tick-marks labels" > setting under Scale of Category (x) axis scale from code? > > Thanks, > Clint ...

Autocorrect Number+space to Number+nonbreaking space
Hi At work, I am forever writing dates in a long(ish) format and have started using non-breaking spaces between the Day and the Month, so that whenever I have dates at the end of a line it keeps the whole date together. Makes it much easier to read. So I dont get 19 May'10 and get 19 May'10 Also would work for phone numbers where they're broken out into area codes. I appreciate that this wouldn't work for the American system where the month comes before the day. I cant seem to be able to do this with AutoCorrect as there is no option for using special char...

Split data into new sheets
I have a (very) long list, sorted by account code. I would like to write a macro that splits the list into separate sheets in the workbook, with a separate sheet for each account code. Ideally, I would also like to rename each sheet to show which account code the sheet contains. I have no idea, though, where to start. Any ideas? Thanks in advance. Hi bernard How many different accounts are in the list (more or less than 250)??? below some code that I use to split files by account numbers where the user has to select a cell within the column that contains the account number... Hope thi...

MS Outlook Add-in/software
Hi. Anyone know a Add-in/software tool which "read through" all emails and catch sender emails, names & telephone numbers? (It should be reading the email signature and the purpose is to store these data in Excel, csv or Access). ...

all long column of cells equal to whatever I put in B2
For some reason I am getting nowhere trying to do a very simple thing. I have a column with many of the cells for a long stretch that I want to be equal to whatever number I put in B2. As I'm building this spreadsheet, it seems to me that it should be a very simple matter to "tell" all those cells to just be equal to whatever is in B2, they using format painter, etc.,.... they don't copy "=B2" part of the previous cells, but instead just copy whatever the number is .... which means that when I change the number in B2, it won't change them. Any ideas? th...

split column
I have a column of data in the following format column A John B. Smith What I want to do is split this column into three separate column so that it will appear as below Column B Column C Column D John B. Smith. Is there a formula that can help me do this. TIA Tlee, If all the names follow the same format (you don't say), you can use Data - Text to columns, using a space as the separator. Make sure that the columns to the right are empty before you start. -- Earl Kiosterud mvpearl omitthisword at verizon period net ---------------------------...

Change column name??????
:rolleyes: *Can somebody help my out?????? I would like to know how to change the column name (A, B, C etc) into a different name...* --- Message posted from http://www.ExcelForum.com/ Good question BH 79 , I also would really like to know this and can't find anything about it :confused:. But I have seen docs in which the columns have names instead of the usual a,b,c-labels... Hope someone knows the answer... --- Message posted from http://www.ExcelForum.com/ "BH79 >" <<BH79.zrnhc@excelforum-nospam.com> wrote in message news:BH79.zrnhc@excelforum-nospam.com......

How do I sum numbers based on other data
I have a spreadsheet with several thousand entries. On one sheet, call it sheet 1, there is One column with about 100 names, another column has number amounts. My other sheet, call it sheet 2, has a list of names. What I would like to do is for each name on sheet 2, sum up the number values from sheet one for that particular name. So the end result would be a column on sheet two that shows the totals taken from sheet 1 for each name. I keep adding new entries on sheet one, so the totals on sheet 2 must always show the updated totals. Any help would be greatly appreciated. I am...

rounding off numbers
Hi everyone! I was just wondering if it's possible to change the parameters of the 'rounding off' function to round up to the next number if the number is something like 24.3 instead of 24.5? Is that possible? Thanks so much for any help!!! DL "DeeElle" <donnaleigh@rogers.com> wrote in message news:a3Brb.3128$HoK.794@news01.bloor.is.net.cable.rogers.com... > Hi everyone! > I was just wondering if it's possible to change the parameters of the > 'rounding off' function to round up to the next number if the number is > something like 24.3 i...