Using macro or formula to format information

ehesh

New Member
Joined
Jun 26, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
ABCDEFGH
1Custom.1Custom.Custom.2123456
2Company 1 UPC: 000191-800-XXX-XXXXCompany.1@email.com
3Company 2 UPC: 0002Company.2@email.com
4Company 3 UPC: 000391-800-XXX-XXXX
5Company 4 UPC: 000491-800-XXX-XXXX Option 2 Ext 391-800-XXX-XXXX (Escalation)Company.4@email.com
6Company 5 UPC: 000591-800-XXX-XXXX Option 591-800-XXX-XXXX (Non Warranty Parts)91-800-XXX-XXXX (Canada)91-800-XXX-XXXX (Paint Sprayers) Company.5@email.comCompany.5.1@email.com
7Company 6 UPC: 000691-800-XXX-XXXX Ext. 1249Company.7@email.com91-800-XXX-XXXX [Subcompany Contractor]Company.7.1@email.com
8Company 7 UPC: 0007Out of BusinessCompany.7@email.com
Sheet2


Rich (BB code):
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"
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top