Using macro or formula to format information

[ehesh]

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

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:

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:

"Email | Email Note | Email2 | Email2 Note",

or in some like the one below or something similar

 Phone Number | Option | Extension | Phone Number Note | Email | Email Note | Phone Number2 | Option for number 2

 

[alansidman]

I can get the data into table format using Power Query. You will need to move the cells around if this works for you to align within specific columns.

Book4
	A	B	C	D	E	F	G	H
1	Custom.1	Custom.Custom.2	1	2	3	4	5	6
2	Company 1	UPC: 0001	91-800-XXX-XXXX	Company.1@email.com
3	Company 2	UPC: 0002	Company.2@email.com
4	Company 3	UPC: 0003	91-800-XXX-XXXX
5	Company 4	UPC: 0004	91-800-XXX-XXXX Option 2 Ext 3	91-800-XXX-XXXX (Escalation)	Company.4@email.com
6	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
7	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
8	Company 7	UPC: 0007	Out of Business	Company.7@email.com
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if Text.Contains([Column1], "UPC") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "Custom", Splitter.SplitTextByDelimiter("[", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","]","",Replacer.ReplaceText,{"Custom.2"}),
    #"Filtered Rows1" = Table.SelectRows(#"Replaced Value", each not Text.Contains([Column1], "UPC")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Custom.1", "Custom.2", "Column1"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Custom.1"}, {{"Data", each _, type table [Custom.1=text, Custom.2=text, Column1=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Custom.2", "Column1", "Index"}, {"Custom.Custom.2", "Custom.Column1", "Custom.Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Column1")
in
    #"Pivoted Column"

 

[ehesh]

I can get the data into table format using Power Query. You will need to move the cells around if this works for you to align within specific columns.

Thank you, but getting the data into table format is not an issue. I managed to pull through so far. The problem is having to move the cells around. If it were 2 or4 or 10 it wouldn't be much of a problem. However, its about 200+ entries that I do not want to manually adjust.

 

[alansidman]

The issue is your raw data is not in any type of normalized sequence. Without having an order to the data, this is the best I can offer. You have no standardization that offers any type of categorization that would allow this.