Move Partial Data from One Column to Another

I am looking for a way to take 5000 lines of data in Excel and take th
last 6 digits of the data and move it into another column.

For Example:          Take the last 6 digits and move to next column
Data 1                              Data 2
435490870985430984 |  430984	
234083209842309820 |  309820
432958739287193872 |  193872	
542389034098430945 |  430945	

With over 5000 lines to move  I need an automated process so that i
does not need to be done manually, any help or ideas would be greatl
appreciated

--
Message posted from http://www.ExcelForum.com

0
6/2/2004 6:44:53 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
480 Views

Similar Articles

[PageSpeed] 2

Are the numbers of the same length (your example show 18 digits which have
to be text) If so, make sure the column to the right is empty and do
data>text to columns
and select fixed width, click next and select the last 6 digits and click,
then click next and select
the format if you want text or general and click finish (takes less than a
minute) and you can record a macro

-- 
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom


"schu1272 >" <<schu1272.178l4r@excelforum-nospam.com> wrote in message
news:schu1272.178l4r@excelforum-nospam.com...
> I am looking for a way to take 5000 lines of data in Excel and take the
> last 6 digits of the data and move it into another column.
>
> For Example:          Take the last 6 digits and move to next column
> Data 1                              Data 2
> 435490870985430984 |  430984
> 234083209842309820 |  309820
> 432958739287193872 |  193872
> 542389034098430945 |  430945
>
> With over 5000 lines to move  I need an automated process so that it
> does not need to be done manually, any help or ideas would be greatly
> appreciated!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
terre08 (1112)
6/2/2004 7:02:04 PM
IF, all data is the same length then simply select the column, do data / Text To
Columns / Fixed Width / Set your marker before the last 6 characters and hit OK.
If not all the same length then post back.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"schu1272 >" <<schu1272.178l4r@excelforum-nospam.com> wrote in message
news:schu1272.178l4r@excelforum-nospam.com...
> I am looking for a way to take 5000 lines of data in Excel and take the
> last 6 digits of the data and move it into another column.
>
> For Example:          Take the last 6 digits and move to next column
> Data 1                              Data 2
> 435490870985430984 |  430984
> 234083209842309820 |  309820
> 432958739287193872 |  193872
> 542389034098430945 |  430945
>
> With over 5000 lines to move  I need an automated process so that it
> does not need to be done manually, any help or ideas would be greatly
> appreciated!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004


0
ken.wright (2489)
6/2/2004 7:11:35 PM
You asked for the last 6, but your example was the first 6.  Either way
it's pretty easy.

For the first 6:

=VALUE(LEFT(A1,6))

For the last 6:

=VALUE(RIGHT(A1,6))

Put this in the column to the right (and change the A1 to the bi
number you are extracting numbers from).  Then copy to all othe
columns.  When you are done, select the entire column and choose copy
then paste special and select Values to remove the formula and replac
with actual values.

This solution converts the 6 digit number from text to a number.  I
you wish to leave it as text, remove the VALUE function.



--
Message posted from http://www.ExcelForum.com

0
6/2/2004 7:28:05 PM
Reply:

Similar Artilces:

Importing Fixed Width data
Hi, I've been extracting information from my company mainframe, and placing fields into a "fixed length record" file, which I then FTP, and import into Excel 2000. Each record contains around 200 fields, and the import process is VERY painful, for two reasons. - many of the fields are single byte, and clicking to set the start/end point of each is a nightmare. - I generally need to put many of my fields into 'Text' format, as much of the data has leading zeros, which need to be retained. Is there a way that I can set up an import 'template', so that I can just...

counting blocks of data
this is a sample of a large block of data I have. A block of data is represented by the letter A until the next cell is Blank. For E.G. row 1 has 2 blocks of data. the 1st block is row1, col3 and 4 and the next block is column 9. row 2 has 2 blocks. Col3,col4 and col5 is the 1st block and column 8 & 9 is the 2nd block. Row 3 has 1 block only. column 6&7 Row 4 has 0 blocks and row 5 has 1 block because all the A's are in adjacent cells. 1 2 3 4 5 6 7 8 9 Row 1 A A A Row 2 A A A A A Row 3 A A Row 4 Row 5 A A A A Thank you all for you...

LINEST
I would like to use the LINEST function on data that is not located in one single row or column. The data that have to be correlated are scattered along the worksheet and I would like to select them manually. How can I do this. If I try =LINEST((E72;F72;G72);(E75;F75;G75),1,1) or =LINEST({E72;F72;G72}; {E75;F75;G75},1,1) an error occurs. How can I input an array of nonadjacent cells in this function. thx Why not use some 'helper cells' with formulas like =E72 to get the numbers into a contiguous range? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme ...

Display column widths
I have just upgraded to MS Money Premium 2005, and all went very well. I have one minor problem, though. I like to open MS Money in Portfolio view, and grab a quick look at my stock figures. Unfortunatley the column widths are set such that the last column (Gain) is crowded off the screen to the right. I'm using a Sony Trinitron 19 inch monitor, so it seems there should be plenty of room for a full screen view. The News and Advisory columns seem to be much wider than they need to be. Is there any way to shrink these down a bit so the Gain column isn't off the screen? I know, I can scro...

Move or Copy Money To Another Laptop
I know this might sound like a stupid question but here goes. I purchased Money 2005 as a download vs a CD purchase. I just purchaced a new laptop and want to move Money to it. How can I install my previously purchased copy of Money 2005 on my new laptop? Steve Copy the downloaded file across to the laptop and install. Copy your .mny file across to the laptop. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. "W1SMC" <...

How can I count dates if few duplicates in a column
I entered few dates in Column C3 to C20, few of them are duplicates. How can I count total number of dates (excluding duplicates), duplicates should be count 1. Try the below =SUMPRODUCT(--(C3:C20<>""),1/COUNTIF(C3:C20,C3:C20&"")) -- Jacob (MVP - Excel) "Tariq Aziz" wrote: > I entered few dates in Column C3 to C20, few of them are duplicates. > How can I count total number of dates (excluding duplicates), duplicates > should be count 1. Hi, Try this =SUMPRODUCT((C3:C20<>"")/COUNTIF(C3:C20,C3:C20&am...

select and move columns by their name in header row
Hi I get a large ws with 70 - 100 cols each month. There is a header row, but the names of the cols are not always in the same order. I want to select 3 or 4 entire columns, and move them to a blank sheet in the same workbook "mdata". This code doesn't work in MS Excel 2007, but I can't figure out how to correct it. Sub cleanup() Sheets("cases-dump").Select date = WorksheetFunction.Match("Procedure Date", Rows("1:1"), 0) icd9 = WorksheetFunction.Match("Pre-op Diagnoses 1", Rows("1:1"), 0) cpt1 = Worksh...

Data validation #20
Hi...I am trying to store a list in a seperate worksheet and the reference it on my other worksheets. When I go to data validation an try to do this, i get the message "you may not reference anothe worksheet or workbook for data validation." My cpu at home has exce 2003, this one has 2002. Any ideas or workarounds? Thank -- Message posted from http://www.ExcelForum.com Hi Try the following: - select your list - create a defined name for this list ('Insert - Name Define') - use this name in your data validation formula. e.g. =Name_list have a look at the following htt...

money 2007: moving from essential to advanced budget
How can I move from essential budget to advanced budget without typing all my information over again? You are unable to the advanced budget in 2007 was designed only to be used as a new budget. Steve "MarkB" <nospam@nospam.net> wrote in message news:uiSNDyuCHHA.3624@TK2MSFTNGP02.phx.gbl... > How can I move from essential budget to advanced budget without typing all > my information over again? > You can access the advanced budget by navigating to money://navigate/newadvbgt in the Money address bar. -- Glyn Simpson, Microsoft MVP - Money http://money.mvps...

How to find the data source
Hi, and Thank you for any help. I have inherited a hand full of Pivot table spreadsheets that Pull data in from an Access DB, and the query used in the spread sheet needs to be modified (it is using some Dates that should not be hard coded). There are a few that I just can not find any Links, or Data Queries, nothing that looks like it is calling out to the data source, except it does prompt me to refresh the data when I open the file. I've found all the hidden sheets, and named ranges (I think) I've looked through all the VBA also. How do I find what I can't see?? Thanks....

Chart with data from multiple worksheets
Is there a way to create a chart that uses data (range) from multiple worksheets in both x and y axis. In my case I have several worksheets for different cities in one file. Within each worksheet are three columns. Column 1 identifies a point of interest in the city, column 2 measures the distance from the airport to said point of interest and column 3 has the elevation of the point of interest. I wish to chart the distances on the x axis and the elevations on the y axis using the data from the each city worksheet directly without creating another worksheet that references all the da...

Displaying or showing a defined cell name in another cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Is there a way to display a defined cell name in another cell? <br><br>I would like to define a name for A1 to be &quot;_1A1&quot; and have that displayed in a different cell rather than the value of cell A1. I am ranking data based on the value in the cell, but would like to have the defined name returned in an adjacent cell: <br><br> A B C D E <br> 1 rank value cell name <br> 2 10 1 10 _1A1 <br> 3 2 ...

Formating columns
Hi, I need help changing the column widths. Column A has a width setting of 15 for rows 1 - 30 but I want rows 31 - 35 to have a width setting of 5 Thank you Columns cannot have different width in different row. If you want to make a table use Word (or wood!) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "dbrumit" <dbrumit@discussions.microsoft.com> wrote in message news:E795F8F4-3CCF-4B96-B055-61EAE53DDEAE@microsoft.com... > Hi, I need help changing the column widths. > Column A has a width setting of 15 for rows 1 - 30 but I...

Monitor Amount Of Data Downloaded
I was wondering if there was any way of finding out how much data (emails) in terms of MB exchange processes/downloads on a monthly basis. On Tue, 6 Feb 2007 21:00:00 -0800, Stephen <Stephen@discussions.microsoft.com> wrote: >I was wondering if there was any way of finding out how much data (emails) in >terms of MB exchange processes/downloads on a monthly basis. Lots, there are 3rd party applications that take your message tracking logs and present them in graph and report formats Quest and Promodag are good packages for the large and small/med orgs respectively. Are there a...

Removing Legacy Data from a PivotChart report
I have a pivot chart report which pulls its source data from SQL Server database. I have created a copy of this file to point to a different SQL Server (exact same tables). When I refresh the report, the chart and pivot are refreshed correctly from the new server. However the original data still remains (as well as the new data) in the drop down selection box for the columns. i.e. the chart is showing ExecuteDate in the ROW area, JobName in the COLUMN area and the number of minutes each job ran each day as the DATA. The graph plots each job showing the duration over the last 30 days. In the...

How to move Exchange server 2000 to a new Exchange 2003 server
I have a exchange 2000 server. I want to move it from this server to the other server that run Exchange 2003 server. Please tell me how to do it. Thanks. Hi Andy, Install the Exchange 2003 server into the same org and administrative group as the 2000 server and using ADU&C move the mailboxes from the 2000 server to the 2003 server. Add replicas of all the public and system folders to the 2003 server. After allowing sufficient time for replication, remove the replicas from the 2000 server and then remove the server from the org. You might want to just stop the services on the 2000 se...

Higher precision: can one get it?
Hi All, I demonstrating the approximation of Pi by successively computing the perimeters of regular inscribed polygons with 2^n sides. Excel's precision was overwhelmed at about 4000 sides. Is there any way to get "double precision"? If worse comes to worse, I'll use something like BigDecimal in Ruby or Perl to get greater precision. -- Regards, Richard Hi Richard, Excel's precision is 15 significant digits. If you need more, you might take a look at the XNUMBERS add-in: http://digilander.libero.it/foxes/MultiPrecision.htm -- Kind regards, Niek Otten &qu...

can i input info in excell one page at a time instead of seeing .
i am new to using excell and my accountant has created a spead sheet for me , i was wondering can i set up a page so that the information i enter is done on its own page and then put in the speadsheet behind the scenes a bit like entering you contact details then it creates a address book Hi, Sure you can , you will need to be more specific on what you want to do, an example will help "barry" wrote: > i am new to using excell and my accountant has created a spead sheet for me , > i was wondering can i set up a page so that the information i enter is done > on its own ...

Columns in Excel
I have the following problem I need help with: Column 1 Column2 Column3 Column4 017290 117110 246781 017450 117118 246790 017451 117119 317891 116127 246761 317990 116128 246771 341772 116129 246780 358381 Say I want to insert a new number into "Column 2." The number 119000. How can I do this and make all the information move automatically to look like the following: Column 1 Column2 Column3 Column4 017290 117110 246780 358381 017450 ...

countif, but only using partial cell contents in the reference array
=MID(a1000,FIND("{",a1000,1)+1,8) This formula returns the portion of the cell that is important to me However, if I want to match a portion of the reference cell array t the portion of the criteria cell, how do i do that? =countif(a1:a100, MID(a1000,FIND("{",a1000,1)+1,8), 0) I don't understand how to change the a1:a100 to to only lookat th MID(a1,FIND("{",a1,1)+1,8) portion of it..... much thanks, jare -- jared ----------------------------------------------------------------------- jaredh's Profile: http://www.excelforum.com/member.php?action=ge...

How do I add muitlple records through one form to a table
Any downloadable example would help greatly. Thanks. On Thu, 6 Dec 2007 17:08:00 -0800, miasma <miasma@discussions.microsoft.com> wrote: >Any downloadable example would help greatly. Thanks. Ummm... The Northwind sample database that comes with Access does this. In fact ANY Access database lets you add multiple records to a table via a Form. What specific problem are you having???? John W. Vinson [MVP] ...

Printing Long Columns
I have a simple worksheet with order numbers in column A and corresponding completion dates in column B. Since there are over 1,000 orders, it takes many pages to print the sheet, with a great deal of paper wasted on the blank area to the right of the two columns. Other than the tedious process of cutting and pasting, is there a way to arrange the information into multiple multiple columns across the page? Try this example on David's site Charlie http://www.mvps.org/dmcritchie/excel/snakecol.htm -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Cha...

Last Number in a column to that is not equal to zero
I have an inventory worksheet that I am having trouble with. The total daily inventory column updates daily. At the end of the column, I want a formula that gives the most recent inventory number. For instance 1 15,358 2 15,358 3 19,520 4 19,520 5 19,693 6 0 7 0 8 0 9 0 10 0 11 0 The final field in the inventory column would read 19693. There will always be a whole number for inventory, so the zeros will be replace with the number once the daily inventory is completed. one thing you can try. If you have a header row add +1 =COUNTIF(A:A,">0") -- Don Guillett SalesAid Softw...

macro that copies all values of columns in column A
Hi, I need a macro that pastes all values from columnas B and C in A Thanks in advances Merry christmas You don't need a macro. In A1 put =B1&C1 and drag down as far as is needed. If you really want a macro, use this: HTH Otto Sub FillColA() Dim c As Long c = 1 Do Until Cells(c, 2) = "" Cells(c, 1) = Cells(c, 2) & Cells(c, 3) c = c + 1 Loop End Sub "1234" <altachicasaudaces@gmail.com> wrote in message news:83e8b616-a94b-470b-8839-addcbcf65c64@m3g2000yqf.googlegroups.com... > Hi, > > I...

Using MFC with another compiler mingw
Hi, I would like to know if it's possible to compile a normal MFC application with mingw (gcc) by converting it to WFC (By SamBlackBurn). Vince wrote: > Hi, > > I would like to know if it's possible to compile a normal MFC > application with mingw (gcc) by converting it to WFC (By SamBlackBurn). > You should consider that to be a complete rewrite of the application. -- Scott McPhillips [VC++ MVP] In addition to what Scott says, I would add "no". I guess, maybe that's not entirely accurate. It is "possible", it would just be an incre...