extracting data from one format to a different format...

I have a large database of company contacts that is formatted/entered as 
follows:

ABC Company, Inc.
John Doe
123 Main Street NE
Des Moines, IA  52000
Phone: 123-456-7890
Fax:  098-765-4321
jdoe@abccompany.com

Each line is in it's own cell, but I need it in column format such as follows:
Company Name - First Name - Last Name - Address1 - Address2 - City - State - 
Zip - Phone - Fax - Email

Any direction as to how I can easily do this?  I'm using either 2003 or 2007 
and have over 7,000 entries like this.  PLEASE HELP as I don't want to spend 
hours into days trying to do this manually!  Thanks.
0
Utf
5/30/2010 12:07:01 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1534 Views

Similar Articles

[PageSpeed] 51

On Sat, 29 May 2010 17:07:01 -0700, Majestic Glory <Majestic
Glory@discussions.microsoft.com> wrote:

>I have a large database of company contacts that is formatted/entered as 
>follows:
>
>ABC Company, Inc.
>John Doe
>123 Main Street NE
>Des Moines, IA  52000
>Phone: 123-456-7890
>Fax:  098-765-4321
>jdoe@abccompany.com
>
>Each line is in it's own cell, but I need it in column format such as follows:
>Company Name - First Name - Last Name - Address1 - Address2 - City - State - 
>Zip - Phone - Fax - Email
>
>Any direction as to how I can easily do this?  I'm using either 2003 or 2007 
>and have over 7,000 entries like this.  PLEASE HELP as I don't want to spend 
>hours into days trying to do this manually!  Thanks.

You need to provide more information.

1.  In your header, you have two Address columns (Address1 and Address2) but in
your example you have only a single address line.  Does this mean that some
entries have seven lines and others eight?  What about those without a Fax? Or
those without and Email?  Are there blank lines or no lines?

2.  How do you tell when you go from one company to the next?

3.  Are there any other variations you haven't mentioned?
--ron
0
Ron
5/30/2010 2:02:27 AM
If you have data for each contact in seven cells (rows 1-7, 8-14,21-27...) in 
Col A
then enter this formula in B1
==INDIRECT("A"&((ROW()-1)*7+COLUMN()-1))
You can then copy it across to Col H and then down to row number n/7 where n 
is the last row with your data...

You can then split the city, state and zip in Col E. You can also find and 
replace FAX: PHONE: etc which you don't want.

"Majestic Glory" wrote:

> I have a large database of company contacts that is formatted/entered as 
> follows:
> 
> ABC Company, Inc.
> John Doe
> 123 Main Street NE
> Des Moines, IA  52000
> Phone: 123-456-7890
> Fax:  098-765-4321
> jdoe@abccompany.com
> 
> Each line is in it's own cell, but I need it in column format such as follows:
> Company Name - First Name - Last Name - Address1 - Address2 - City - State - 
> Zip - Phone - Fax - Email
> 
> Any direction as to how I can easily do this?  I'm using either 2003 or 2007 
> and have over 7,000 entries like this.  PLEASE HELP as I don't want to spend 
> hours into days trying to do this manually!  Thanks.
0
Utf
5/30/2010 2:47:01 AM
Reply:

Similar Artilces:

Excel Regional Date Format Options
A client of ours in NZ is complaining that date format options for English (New Zealand) have changed from older versions of excel (they are using 2003) Some of their spreadsheets have dates formatted as dd-mmm-yy, mmm-yy and dddd,dd,mmm but these options do not exist anymore. Is there anyway to add options to this list without using the custom format option? Thanks, Jesse I just compared the Excel 97 and Excel 2003 built-in date formats and they are mostly unchanged. 2003 has a few more but I don't think there were any subtractions. The formats dd-mmm-yy and mmm-yy are righ...

When I am in one cell highlight another
Hello, When I am in one cell highlight another. Lets say that if my cursor is in A1 I want D1 highlighted or with another cursor on it. Then if I move to A2, D2 should be the one with another cursor or highlighted. Thank you for your help, Jose Juan Diaz hi, Jose Juan ! > When I am in one cell highlight another > ... if my cursor is in A1... D1 highlighted or with another cursor on it. > ... if I move to A2, D2 should be the one with another cursor or highlighted. 'put' a cursor on non-active-cell... [I don't think it's possible] :( to highlight 'D' wh...

Copy field data to multiple places
Newbi here.... I have a access 07 file of about 1000 records (rows) and a field (column) I'll call the "project number". All the records do not have the project number inserted as of yet. Is there a simple means to insert a project number in say 50 records at a time, another project number in another 75 records etc. Copy/Paste will do it but may take months to enter. Any suggestions appreciated. TIA On Wed, 27 Feb 2008 15:31:05 -0500, "Meebers" <justme@idontkno.com> wrote: >Newbi here.... I have a access 07 file of about 1000 records (rows) and ...

Pivot Tables & changing data
Hi - I have never used pivot tables in Excel before so hopefully what I am going to ask is possible and not too complicated for me.... :o) I am working with Excel 2003. I have a pivot table already set up and the information is pulling data from a row titled "sum of Subscriber". I added new data in a new column from the main spreadsheet and I would like to pull the data from there. Its titled "Adj Subscriber". Is it possible to switch it? If so, how? Thanks, Anna Marie Anna wrote: > Hi - I have never used pivot tables in Excel before so hopefully what I am &...

How to filter to show only one message from a specific sender/subject
I would like to filter out all but one email with the same subject line from a specific sender. I subscribe to a message board that sends an email every time a posting is made to a thread, instead of just the first posting after I have read the thread. Is there a way for filter out the duplicate messages and just keep the oldest email? Thanks, Brian ...

how to edit my x-axis data on a line graph
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel my x axis data on my graph is just showing numbers 1 2 3 4 5 6 7 8 9 10 11.... and it is suppose to show the years. I can't figure out how to change these values ...

Data from Access query to Excel
To pull data from an Access 2003 database, I have created the queries in Access, then import into Excel. The problem is that all the numbers that are pulled into Excel are text and need to convert them into numbers to run formulas on. I have converted a few sheets by hand, but, some have will over 50,000 rows. Is there a function to select all number colums (the colums are the same through out the sheets) and convert? Thanks There are instructions here for converting text to numbers: http://www.contextures.com/xlDataEntry03.html You can select all the columns, and only the num...

How to delete duplicate data
Hi, I am using excel to consolidate monthly room booking data. I have a date column and time column. May I know how to to delete those rows which contains duplicate data with same date stated in the date columnand and same time range in the time column? Thank you. Data>filter>advanced filter, unique records only and copy to another location -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "PL" <PL@discussions.microsoft.com> wrote in message news:F45DAE2B-252D-4E17-81D8-0F4C060BCB21@m...

Summary of Difference between dates in years, months, days
I need to calculate the difference between 2 dates and then total them. Here's what I have so far: From To Length of Service 01/09/2003 31/01/2010 6y 4m 30d 01/06/2000 30/11/2002 2y 5m 29d Total of Service: ?????????? I've used the following formula to calculate the total days worked: =DATEDIF(A4,B4,"Y")&"y "&DATEDIF(A4,B4,"ym")&"m "&DATEDIF(A4,B4,"md&...

Problem with printing listbox bigger than one page
Hi, I created form "Invoice". It contains few list boxes (like: Brand, Product, Price, etc.). Each list box is big enough to display 50 items. It works in this way: user is chosing product from table Products_tbl and clicks on "Add to invoice" button which adds to each list box new item containg apropriate information about chosen product. At the end, user can print the form. Before it was limited, and user could put max. 50 products on one invoice, but I wanted to get rid of this limitation. I did it in this way: after 50th item, with each added product I extend list boxe...

Compressed Outlined Data Copy
I need to copy only the data that is compressed in Grouped rows and not all the rows when they are expanded. When I copy and paste the compressed data, I get all the data that is within the group, and not just the compressed data. For example: I need just the following (which is compressed): Ship $ Line $5,278.00 CYP Total $133,122.00 TI Total $34,660,135.00 Vic Total $34,798,535.00 Grand Total when I copy it (D2:E17, but only showing 5 lines because of the compression), it pastes 16 lines: Ship $ Line $123.00 CYP $44.00 CYP $4,444.00 CYP $667.00 CYP $5,278.00 CYP Total $123,124.00 TI $...

Conditional formatting help #4
My problem is that, that i want to ignore blank i mean i had set a conditional formatting say A B C D 24.9 25.9 25 25.8 22.6 23.4 22.5 23.3 If value in ColA is less than value in ColC, cell A1 is shaded blue OR if value in ColB is greater than value in ColD, cell B1 shaded blue. I have done above formatting but my problem is that if i dont enter anything in colC then also colA is shaded in blue similarly if i dont enter any value in colD then also col B is shaded.I mean i want to ignore the blank.I need , if col C is blank then the Col A must be normal .& if col D is blank & i ent...

Combine part of text from one col to another
I'm trying to combine only part of a string from one column with all th text of another column. For instance I want to create usernames to log into a network. If col has Bill and col2 has Smith I want to get just the B from col1 an combine it with col2 to make smithb in a new column. Thanks in advance -- Message posted from http://www.ExcelForum.com Try this: -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== =B1&LEFT(A1) "epac135 >" <...

inverting data #2
Please help, I'm stuck! I conducted a survey where respondants each gave answers to 30 questions using a Likert scale i.e. they answered either 1,2,3,4 or 5 to each question. The data is in the following form: Question # q1 q2 q3 q4 q5 1 2 4 2 1 2 2 4 1 2 1 4 3 4 4 2 1 4 4 2 3 2 2 5 5 2 3 4 5 3 My problem is that half of the questions were asked in a negative direction to avoid possible response bias. For the answers to these questions, I need to reverse the answers. If the repondant answered 1, I want to record 5, if 2 then 4, if 3 then 3, if 4 then 2, if 5 then 1. Is there an...

VFW, webcam, image format
Hello, I'm trying to grab pictures from a webcam with Video For Windows (VFW). The source code I use is the following: HWND _capwnd = capCreateCaptureWindow("Capture",WS_POPUP,0,0,1,1,0,0); capDriverConnect(_capwnd,0); capGrabFrame(_capwnd); capEditCopy(_capwnd); OpenClipboard(NULL); HBITMAP hBmp = (HBITMAP)GetClipboardData(CF_BITMAP); CloseClipboard(); BITMAP bmp; GetObject(hBmp, sizeof(BITMAP), &bmp); int size = bmp.bmWidth*bmp.bmHeight*bmp.bmWidthBytes; unsigned char* data = new unsigned char[size]; GetBitmapBits(hBmp,size, data); ...

extraction code from celd
Hi :) I have a Excel problem :confused: MY QUESTION IS: if A5 = "JhoN FreD SmitH ChonG then A6 = "JNFDSHCG" I would like have got solution ;) please !!! thank you ver much nando4000@latinmail.co ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com You could create a User Defined Function: '=============================== Function GetCode(rng As Range) As String Dim i As Integer Dim str As String i = Len(rng.Value) For i = 1 To i str = Mid(rn...

Importing One Period
Our company operates on a 13 period year instead of 12 months. However, the budget import process appears to be the same for periods or months. We would like to import a budget for just period 13. We tried leaving blanks in the period 1 - 12 accounts in excel and imported to a test company and that deleted all budgets, we also tried zeros, which did as we expected, inserted zeros. The budget file used to import existing budgets for periods 1 -12 has become corrupt and we do not have a backup for this file. In this file, we created formulas that allow us to use %'s to update ...

compare two columns with different ranges in two worksheets
I need to compare two columns of data in two different worksheets and display a third one. Here it is an example: -(worksheet1!A1:A10), (worksheet1!B1:B10) and (whorksheet2!C1:C25) -this is my query, if C5 is already in (A1:A10) I want to display B5 in worksheet2!D5 I think it is tricky because you need to identity which row in the A1:A10 is equal to C5 to display B5 and the range are different. you could save my day chris90 In worksheet2!D1: =if(isna(vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)), "", vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)) HTH Kostis Vezerides brilliant, ma...

Retreiving data
We move mailbox on a new server but somethimes we receive the following message: "outlook is retrieving data from the OLD exchange server" Can anyone help me -- Dambo On Thu, 1 Feb 2007 01:21:01 -0800, Damb0 <Damb0@discussions.microsoft.com> wrote: >We move mailbox on a new server but somethimes we receive the following >message: "outlook is retrieving data from the OLD exchange server" > >Can anyone help me What steps did you do to decommission the old server? If you give us an idea of the version of the old and new ones we can post the right help ...

Multiple recipients for one address
Total newbie question. I work for a company with a dedicated service department. I need for up to 5 people to be able to get a copy of a message that comes in to the address "servcie@ourdomain.com". I tried adding this address to each of the users, but I can't give one address to multiple users. I don't want to create a "Service" user and have them check that account. I just want the emails to be routed from the service@ address to each users' inbox. Total newbie, so detailed answers would be appreciated. Thanks in advance. Create a mail-enabled Grou...

Help Required with Macro to Manipulate Data
I have a spreadsheet(s) with the following data on it (this is imported from a text file) A B C D 1 Branch Date Total 2 4501 030204 29 3 4 4501 030204 14 5 6 4502 030204 331 7 8 4502 030204 52 9 10 4503 030204 54 11 12 4503 030204 85 Hi Alay and what do you want to achieve :-) -- Regards Frank Kabel Frankfurt, Germany "Alan T >" <<Alan.T.12jthe@excelforum-nospam.com> schrieb im Newsbeitrag news:Alan.T.12jthe@excelforum-nospam.com... > I have a spreadsheet(s) with the following data on it (this is imported ...

Printing results on different pc and different printer
Hi, regarding printing functions in windows i am a newcomer. Thats why i dont understand some phenomenon : I am working with MFC. My report uses Times New Roman and Arial. Report was developed under PC A with Epson Printer. PC B using Epson produces the same output. PC C with Lexmark produces a report with tiny capitals, that are barely readable. PC A with lexmark Printer produces a readable result, but different to Epson Printer. What did i forget to implement in my program? How can i produce a result, that is independable from the printer used? Thanks in advance, Matt Are escape seque...

MM from Word in HTML Format
I am using MS Office 2002. When I do a mail-merge from MS Word to email, it works fine as long as I use 'Plain Text' as the format. It accesses Outlook and sends the messages. However, if I use HTML format, it acts as though it is sending something, but the messages never get sent. Does anyone know what my problem might be? Brian, I seem to have the same problem in Offic 2003. When I do an email merge from Word, the screen flickers at the end but there is no record of the emails having been sent in Outlook, i.e., they are not in the Sent folder. I've tried looking around...

How do I format a cell to auto date
I would like to have a date automatically enter itself each time I open the spreadsheet. Is there a way to do this? Any help would be appreciated. Thanks Hi George Use a function like this one for todays date =TODAY() -- Regards Ron de Bruin http://www.rondebruin.nl "George" <George@discussions.microsoft.com> wrote in message news:7EF21130-EBD9-41C2-8CB5-5723BE40CBB1@microsoft.com... >I would like to have a date automatically enter itself each time I open the > spreadsheet. Is there a way to do this? Any help would be appreciated. > Thanks ...

Remove Data Format Change
I have a program that puts a database's data into an excel spreadsheet. The problem is that Excel is constantly changing the numbers to dates, which corrupts the data. I know I can switch the column data type to Text and that will fix it, but the problem is that when the data is written to the spreadsheet it overwrites that column data type and Excel does the formatting that changes my data. How can I turn this off so Excel stops trying to recognize data types and change their format? Please help because this is causing major problems for us. Thanks, ACFalcon >>I have a ...