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
1529 Views

Similar Articles

[PageSpeed] 56

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:

How can I Show data as a % of Sub Group in a Pivot Table
I want to show a column of data (% of City) as a % of a sub group total. Here's my example of how the pivot table should look: State City Carrier Sq Yards % of City (Sq Yards2) Alabama Birmingham Carrier 1 10 20% Carrier 2 20 40% Carrier 3 10 20% Carrier 4 10 20% Birmingham Total 50 100% Mobile Carrier 1 10 25% Carrier 2 10 25% Carrier 3 1...

Pivot Tables filtering data
Using Excel, I have query(using an ODBC connection) of all customer order detail over a two year time period. The detail for each customer is different in that some customer have multiple orders, and the amount of individual sales parts they order may be 1 or 100. My query gathers detail by order line item. We were able to provide management with comparative sales data. Now that we have shown this report to management, they want three things that I'm unsure how to provide. 1) Customers that are new in the current year2) customers that had orders in both years, 3)customers that ...

How do I plot data on a map in excel
I am using Microsoft office standard 2003. I wish to plot information on a local area map within the Clapham Park area, SW2. Hi, There is no built-in functionality for this. You would need a dedicated Mapping program with UK based information. You can however simulate the chart. If you have an image of the map you could use that as the plotarea pattern. You could then use a xy-scatter graph to plot information over the map. You would have to determine the coordinates by trial and error. Cheers Andy Byron wrote: > I am using Microsoft office standard 2003. I wish to plot informat...

Combine data from multiple rows onto one row in separate columns
My table looks like this: Sample_# data1 data2 data3 data4 etc.. 0000001 0.1 0.2 0000001 2.5 0000001 0.5 0000002 0.2 0.2 0000002 3.1 0000003 0.1 0.2 I receive data at different points in time and it thus I end up with multiple sample_#'s and various data columns filled in. I would like to combine all the data for each sample into one row. I would like it to look like this: Sample_# data1 data2 data3 data4 etc.. 0000001 ...

How can I use a form (Excel) to add a column of data to the right.
How can I use a form (Excel) to add a column of data to the right of a list? It's a simple matter to add a column to the left of a list by writing a macro that inserts a column and then pastes the values. However I want to have the data pasted on the right of a list, if only so that I can easily run a chart from it. ...

Can I insert Google Calender data into Publisher document?
How can I configure my Publisher document to use the data from Google Calendar? I would like to use the RSS feed from Google Calendar. Creating an RSS Feed http://blogs.msdn.com/lisawoll/archive/2004/11/19/266972.aspx -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "thpope" <thpope@discussions.microsoft.com> wrote in message news:CB5CDF70-1B8E-45E1-B17B-7D496CD96F36@microsoft.com... > How can I configure my Publisher document to use the data from Google > Calendar? I would like to use the RSS feed from Google C...

Data Validation Dropdown #4
I don't see the dropdown list on one worksheet. if I copy the cell an paste special Validation to another worksheet, then the dropdown lis shows. Tools/options/show All checked. tried restart PC also. the trouble (dropdown list doesn't show even though Data validatio object is OK) happens only to one worksheet in this one workbook. any one interested to see the workbook with the troubled worksheet ca drop me an email. thanks al -- jliu200 MS programmer for financial industr ----------------------------------------------------------------------- jliu2000's Profile: http://www...

Pivot Table data values
I need make a pivot table using the values as the data. I used to be able to do this, but now it only sums, counts or calculates. Is there a way to return the data values? ...

Email does not post in the Inbox until click on a different folder
I have a machine that is running on Windows XP and has Outlook 2000 installed. Everything seems to be working properly except no email comes or leaves without clicking on a different folder than the inbox. If the inbox has focus no mail will arrive. If you click on a different folder and change the focus, the emails will arrive. If you compose a new mail message and click send, the message goes to the outbox and stops. If you click on a a different folder than the one that has focus the mail will go out. I have run the office repair. I have removed office and re-installed it w...

Leading ' in cell values when data is Exported to Excel 2002
Hello, when exporting data to Excel, all of the cells have a leading ' . Any ideas on how to keep this from happening? I have not seen this with earlier versions of Excel and am experiencing this problem when Exporting data from WinRunner and from MS Access through VBA. Much appreciated, thanks. Hi you may try after importing the following line of code range.value=range.value "Jimmy" wrote: > Hello, when exporting data to Excel, all of the cells have a leading ' . > Any ideas on how to keep this from happening? > > I have not seen this with earlier v...

compare two tables for mismatch data
I have to do a comparison of two tables. Both tables have the same exact headers. The first table is the standard rates template which is linked from excel. The second table is coming from a query off our AS400 system. So basically we are needing to compare with the standard template vs what was inputted into the system. Basically auditing the work inputting, looking for typos and mismatches. What would be the easiest method to use considering we are having to check about 20 fields. ...

Re: extract info from form
U�ytkownik "Jan Kowalski" <osoba@microsoft.com> napisa� w wiadomo�ci news:... > > U�ytkownik "Jan Kowalski" <osoba@microsoft.com> napisa� w wiadomo�ci > news:... >> >> U�ytkownik "Graham R Seach" <gseach@REMOVEaccessmvp.com> napisa� w >> wiadomo�ci news:FC873C2F-B5C3-425B-B420-1237B68E1B5E@microsoft.com... >>> Wes, >>> >>> There are easy ways of doing this, but you'll have to be a bit more >>> specific before we can really help you. >>> >>> Regards, >>>...

How do I format cells to a specific number of digits?
I am trying to format a column so I can enter 5 digits only. It has between 5 and 7 digit numbers in it right now and I need to delete the remaining ones. It is about 350 numbers, so it would be a lot of work to go into each cell and delete the remaning numbers. Is therea way I can format the whole column to allow 5 digits? The same happened with letters. I had codes in it that consisted of numbers and letters and the client only wants the first two letters to remain. How can it be formatted so only 2 characters are allowed to keep me from having to enter each cell separately? Hi form...

date format #9
Hi everyone Is there any way to add automatically "rd" or "st" or "th" etc after the date. I have a date in A1 as "30-01-2001" and I need to show in B1 as "January 30th" Hope someone has an idea Thanks Toms --- Message posted from http://www.ExcelForum.com/ Hi SMILE! One Way. Use: =DAY(A1)&IF(AND(MOD(DAY(A1),100)>=10,MOD(DAY(A1),100)<=14),"th",CHOOSE (MOD(DAY(A1),10)+1,"th","st","nd","rd","th","th","th","th","th","th")...

How do I get a list of data on one sheet into the form on the fir.
I have a timecard form on the first tab of a workbook & the various employee crew combinations on each of the subsequent tabs (each crew on a seperate tab). How do I format the for to get the employee name info from the individual crew tab required? Let me clarify my question - I am trying to use the first tab (the form) as a constant form & just change the employees names in that specific field based on the crew I want to generate the timesheet for. The crew lists are on the sugsequent tabs. I would like to be able to select the crew & print the form with only those emp...

sort special text/numbers in format with many dots
Hi I need your help with sorting in Excel! I have mani Text fields with numbers into it. As example: 1 5.1 1.2 10.2.1 1.2.3 2 2.1.5 3 3.2.1.1 3.3.1.1.1 And it should sorted like this 1 1.2 1.2.3 2 2.1.5 3 3.2.1.1 3.3.1.1.1 5.1 10.2.1 How can I sort this like numbers? My problem is, that not all Numbers have the same format as x.x.x.x! And I can't change this Text-Fields to Numbers, because 10.6.1 looks the like 37052 :-( Any suggestions? Thx Marco Hi With your data in column A, insert a blank column at B. In B1 enter =IF(ISNUMBER(FIND(".",A1)),LEFT(A1,FIND(".&q...

Getting all Sproc through one query
Hi all, can anyone help in getting below information from all stored procedure from one DB we have 100 procedure in one DB of sql server 2005.instead if of manully getting info for each procedure i want all below details in one shot or one query Procedure Name : Input Parameter : Output Parameter : Called By : Calls : Dependent tables : Thanks in advance ...

Drawing static/edit control in different colors
Hi All, This should be a simple question, but it is possible to, control the color of text written to static text or an edit box? I need to do this at during InitDialog and while dialog is 'up' (DoModal). As an aside, can change the color of static text just based on the resource ID? i.e. not creating a variable for the control in the dialog class. Thanks in Advance! Corey. Corey Wirun wrote: > Hi All, > > This should be a simple question, but it is possible to, control the color > of text written to static text or an edit box? I need to do this at during > In...

Remove format link?
Is there a way to remove the format link between fields in Modifier? Help just shows how to set it and the usual stuff doesn't seem to be working. ...

Sent messages are one hour off...why?
Every time I send a message, the shown "sent" time is exactly one hour early. When people reply, their time is now one hour off as well. I've checked the time in the lower right corner and it is correct. Is there a way to fix this? I'm using Win XP and Office PRo 2000. Thanks, Rob. also check your time zone and the daylight time settings - you can access it by double clicking the time in the tray. also very it's correct in outlook - tools, options, calendar. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote ...

Newbie: In a CRichEditCtrl, FindText more than one occurrence?
This is a multi-part message in MIME format. ------=_NextPart_000_004B_01C36B32.6412DB00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have been looking at three books and making many C++ errors for two = day now for answers to my questions. If I can help it, I save this as my = last resort. Anyway, this is my last resort. I have two questions: 1) I have a CRichEditCtrl class. I can use the FindText function to find = the first occurrence of the substring but I don't know how to find the = second++. (Or the CString or the <st...

printing 2 pages in one A4
Please help. How do I print in excel. I have data of 5000 rows and each row has 3 columns. Each A4 page can take around 80 rows of data. In order to save on paper, I was wondering if I could divide the page to accommodate 6 rows by printing rows 1-80 on left side of an a4 page and then rows 81-160 on the right side of the page. And then rows 161 to 241 on the left and rows 321 on the right side of page 2. And so on until the total 5000 rows are printed. I know I can do this page by page one at a time using the printscreen option but I was hoping I could set it up so the printing could be d...

Find what control is using a data item
I built a form, then deleted 2 columns from the source table and now I get a popup asking for the value of those 2 columns. The problem is, I don't use those columns so I need to find what on the form is refferencing the deleted columns. I have looked at the control drop down on the properties window and the tab order window and can't find a control with either one of the missing column names. How do I determine what is trying to refference the deleted columns? I found the problem, the column was still referenced in the underlying query that fed the form. "MeSteve"...

how do I enter more than one line in the same Excel Cell by using.
I want to have more than one line of text in the same Excel Cell without using the wrap text format option. For example, I want to put an entire address in the Cell by typing in the name of the person, then hit enter, type the steet address, then hit enter, then type in the city, state, and zip. Use alt-enter to put in a line break. "vsimmons" wrote: > I want to have more than one line of text in the same Excel Cell without > using the wrap text format option. For example, I want to put an entire > address in the Cell by typing in the name of the person, then hit en...

Word doc format
I tried to email a word attachment written on office for mac 2008 home version, but the recipient couldn't open the doc. recipient using windows xp. rlciii@officeformac.com <rlciii@officeformac.com> wrote: > I tried to email a word attachment written on office for mac 2008 home > version, but the recipient couldn't open the doc. recipient using windows > xp. If they don't have a recent version of Windows Office, they may not be able to open a .docx file. Choose File > Save As and save as a .doc file. -- Adam Bailey | Chicago, Illinois adamb@lull.org | Finge...