Import/Text to Columns Trick?

[cchrisj]

I have several hundred records that I would like to paste/import into Excel. Currently the records are listed vertically in Word, each with what will become the column headings and a colon, then the unique data. I have pasted a sample below, with the column headings in bold (each of the records includes the same column headings, even if there is no unique data for the record)

~Company: XYZ Company
Structure: Sole Proprietor
Owners: XYZ Jones
Primary Contact: John Jones
Email: [email protected]
Alt. Email:
Business Phone: 1111111111
Cell Phone:
Home Phone:
Fax:
Address: 1234 Main St
Great City, ST 09876
United States
Description: XYX Description. XYX Description. XYX Description. XYX Description. XYX Description. XYX Description.
Website: www.jjonescompany.com

~Company: XYZ Company2
Structure: Sole Proprietor2
Owners: XYZ Jones2
Primary Contact: John Jone2
Email: [email protected]
Alt. Email:
Business Phone: 2222222222
Cell Phone:
Home Phone:
Fax:
Address: 1234 Main St
Great City, ST 09876
United States
Description: XYX Description. XYX Description. XYX Description. XYX Description. XYX Description. XYX Description.
Website: www.jjonescompany2.com

I have attached a sample file with the columns identified.

What is the easiest way to get all of the records in Excel?

[¯\_(ツ)_/¯]

Hopefully this works. Run it against a copy of your data first!

The code assumes your re-organized table is on the sheet named "Sheet2"

1. Add a new worksheet and call it "RawData" (without the quotation marks, of course)
2. Copy the data from Word and paste into cell A1 of the RawData sheet. If your data is as you say, it should paste the first company into A1:A15, then second company in A17:A31, etc.
3. Paste the code shown below into the RawData sheet module in the VB Editor
4. Run the code.

Please Login or Register  to view this content.

[jindon]

See if this works.

Please Login or Register  to view this content.

[cchrisj]

I followed the instructions, and didn't get the expected/desired ressults. A file is attached as the result ... (.xlsm file)

Also included is a sample Word doc of a few of the records, so you can see how they are formated - perhaps this will help.

[63falcondude]

Here's a formulaic approach.

Simply paste the raw data into the 'Raw Data' sheet then the formulas in the 'Formatted Data' sheet take care of the rest.

Currently the 'Formatted Data' sheet is set up for 100 companies. Drag the formulas further down if you need more.

B3 =TRIM(MID(INDEX('Raw Data'!$A:$A,16*(ROWS($1:1)-1)+COLUMNS($A:A)),LEN(B$2)+3,999))
Drag through K3.

L3 =MID(INDEX('Raw Data'!$A:$A,16*(ROWS($1:1)-1)+11),LEN(L$2)+3,999)&" "&INDEX('Raw Data'!$A:$A,16*(ROWS($1:1)-1)+12)&" "&INDEX('Raw Data'!$A:$A,16*(ROWS($1:1)-1)+13)

M3 =TRIM(MID(INDEX('Raw Data'!$A:$A,16*(ROWS($1:1)-1)+COLUMNS($A:N)),LEN(M$2)+2,999))
Drag through N3.

Drag all formulas down as far as needed.

See attachment.

[¯\_(ツ)_/¯]

@cchrisj...

1. You uploaded a document containing names, phone numbers and e-mail addresses for hundreds of people/businesses. I'd highly suggest removing it.

2. Your source data is inconsistent. About 75% of the time your business records contain 15 rows, but sometimes it's 14, 16, or even 20+. Using a formula to handle those exceptions (25% of the time) is probably going to be tedious. This also makes macros more complex to account for the unexpected rows of data, but probably more manageable.

3. Your original sample data in Excel was all in one column and included a blank row between businesses. The actual data doesn't have a blank line between them, and when copied from Word and pasted into Excel, it ends up in two columns except for the Address data and sometimes the description data (refer back to #2).

The key to data entry and organization is consistency. Otherwise it's "garbage in - garbage out".

[63falcondude]

Whoops didn't see post #4 before posting post #5.

My response was based off of post #1.

[6StringJazzer]

I followed the instructions, and didn't get the expected/desired ressults. A file is attached as the result ... (.xlsm file)

Also included is a sample Word doc of a few of the records, so you can see how they are formated - perhaps this will help.

The data in the Word file may be considered private by the owners, especially home and cell phone numbers. I suggest you remove your attachment. You may edit it to anonymize the private data and repost.

[cchrisj]

ooops. Sorry. You're right. Trying to figure out how to remove it.

I guess you did it.

[jindon]

Now I need to see your word file.

Here's a word file I've tested that I copied the data in your 1st post.

[jindon]

Sorry, but my code runs only on Windows, not on Mac OS.

[cchrisj]

Yes, I am still working on this. I have "scrubbed" the data, and all of the records are identical. They look like this:
--

Company: ABC Company
Structure: LLC
Owners: Owner A, Owner B
Primary Contact: Mr. Owner
Email: [email protected]
Alt. Email:
Business Phone: 111-11-1111
Cell Phone:
Home Phone:
Fax:
Address: 4321 Main St, City, St 11111
Description: (This is a open text field including a paragraph of information).
Website:

Company: XYZ Company
Structure: LLC
Owners: Owner 1, Owner 2
Primary Contact: Ms. Owner
Email: [email protected]
Alt. Email:
Business Phone: 222-22-2222
Cell Phone:
Home Phone:
Fax:
Address: 1234 Main St, City, St 22222
Description: (This is a open text field including a paragraph of information).
Website: www.companysite.com

--
There should be 13 columns and note that "Address" and "Description" may have multiple lines (separated with a comma or manual break - not a return/enter). So, each record has 13 data points, and the 14th line is blank. After each colon there is a tab, then the data/entry (if available).
--
Example: "Company:[tab]XYZ Company"

...
"Alt. Email:[tab]"

--
I tried to run the code you provided ... didn't work.

[JeteMc]

Based on the data presented in post #12 you could use the following approach:
1) Populate column B on the 'Raw Data' sheet using: =IF(A1="","",LEFT(A1,SEARCH(":",A1)-1))
2) Populate column C on the 'Raw Data' sheet using: =IF(A1="","",MID(A1,SEARCH(":",A1)+2,LEN(A1)-SEARCH(":",A1)))
3) Populate column D on the 'Raw Data' sheet using: =COUNTIF(B$1:B1,B1)
4) After the column headers are placed in row 2 on sheet2 the following array entered formula* may be used to fill the range B:N as modeled in the attached file:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Note that I am assuming the functions used in the 2004 MAC version (which I have never used) are the same as those used in the 2003 PC version.
Let us know if you have any questions.

[Bo_Ry]

Please see attached.
I copy data to sheet Txt column A.

B3 Press Ctrl+Shift+Enter
=IFERROR(SUBSTITUTE(INDEX(Txt!$A:$A,SMALL(IF(IFERROR(SEARCH(B$2&":",Txt!$A$1:$A$99)=1,),ROW(Txt!$A$1:$A$99)),ROWS(B$3:B3))),B$2&": ",""),"")

[cchrisj]

This appears to have some promise. I took the actual data and pasted it into ColA of Sheet "Txt." (A:1 thru A4582).

However, only the first 7 (seven) records show up in Sheet2. And, The company name of record 8 (eight) is displayed, but none of the rest of the information.

The good news is, the first 7 (seven) records look awesome. Any ideas on getting the remaining records to show up in Sheet2?

[Bo_Ry]

Change $A$99 to $A$9999

=IFERROR(SUBSTITUTE(INDEX(Txt!$A:$A,SMALL(IF(IFERROR(SEARCH(B$2&":",Txt!$A$1:$A$9999)=1,),ROW(Txt!$A$1:$A$9999)),ROWS(B$3:B3))),B$2&": ",""),"")

[cchrisj]

Eureka! I had tried that, but only in one place. The world is back to normal. Thanks so much.