I was recently handed in a text file I am trying to create a spreadsheet or CSV from the contact information. I have noticed that I have "missing spaces" and cannot find a method of shifting the cells to properly store the details.
I have included the following as an example of the text
I managed to make a delimited-type version of this by replacing single line breaks with a delimiter and double line breaks with a single line break. However, the fact that each company/vendor has a different method of contact, that not all of them have a phone number or email address, and that some have more than one phone number and/or email makes it difficult for me to place it on a formatted table. Mainly the way I am trying to have this formatted something like this is:
When I try to convert text to columns since not all companies have the same amount of information I end up with information in the wrong fields. I am looking to some sort of formula on a separate sheet or a macro that will reformat the information under its respective column. I do realize that I might need multiple entries for the phone numbers and emails for example:
or in some like the one below or something similar
I have included the following as an example of the text
Rich (BB code):
Company 1 [UPC: 0001]
91-800-XXX-XXXX
Company.1@email.com
Company 2 [UPC: 0002]
Company.2@email.com
Company 3 [UPC: 0003]
91-800-XXX-XXXX
Company 4 [UPC: 0004]
91-800-XXX-XXXX Option 2 Ext 3
91-800-XXX-XXXX (Escalation)
Company.4@email.com
Company 5 [UPC: 0005]
91-800-XXX-XXXX Option 5
91-800-XXX-XXXX (Non Warranty Parts)
91-800-XXX-XXXX (Canada)
91-800-XXX-XXXX (Paint Sprayers)
Company.5@email.com
Company.5.1@email.com
Company 6 [UPC: 0006]
91-800-XXX-XXXX Ext. 1249
Company.7@email.com
91-800-XXX-XXXX [Subcompany Contractor]
Company.7.1@email.com
Company 7 [UPC: 0007]
Out of Business
Company.7@email.com
I managed to make a delimited-type version of this by replacing single line breaks with a delimiter and double line breaks with a single line break. However, the fact that each company/vendor has a different method of contact, that not all of them have a phone number or email address, and that some have more than one phone number and/or email makes it difficult for me to place it on a formatted table. Mainly the way I am trying to have this formatted something like this is:
Rich (BB code):
Company Name | UPC | Note (like out of business etc) | Phone Number | Option | Extension | Phone Number Note | Email | Email Note
When I try to convert text to columns since not all companies have the same amount of information I end up with information in the wrong fields. I am looking to some sort of formula on a separate sheet or a macro that will reformat the information under its respective column. I do realize that I might need multiple entries for the phone numbers and emails for example:
Rich (BB code):
"Email | Email Note | Email2 | Email2 Note",
or in some like the one below or something similar
Rich (BB code):
Phone Number | Option | Extension | Phone Number Note | Email | Email Note | Phone Number2 | Option for number 2