Putting data into separate cells from one cell - breaking up an untidy list

Zakky

Board Regular
Joined
Mar 26, 2016
Messages
152
Office Version
  1. 365
Platform
  1. Windows
hi, is there a way to get the data below (which is in one cell) into separate cells going down so it's nice and tidy. At the moment i'm copying the data 5 times and deleting the unwanted text to have one day in each cell: The problem is it's not consistence with the separators. some have a full stop, space, semi colon or space. I want to delete the separators and just be left with the text. Thank you

Monday, Tuesday; Wednesday. Thursday Friday

Monday
Tuesday
Wednesday
Thursday
Friday
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
With Power Query

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Column1",Splitter.SplitTextByAnyDelimiter({".",";",","," "})),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Text.Trim, type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Trimmed Text",{"Attribute"})
in
    #"Removed Columns"
 
Upvote 0
With Power Query

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Column1",Splitter.SplitTextByAnyDelimiter({".",";",","," "})),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Text.Trim, type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Trimmed Text",{"Attribute"})
in
    #"Removed Columns"
alansidman, thank you for replying. This is a a bit heavy for me, as i was expecting an Excel function. I don't use Power Query.
 
Upvote 0
Code:
Sub As_Per_Your_Example()
Dim a
a = Split(Replace(Replace(Replace([a3], ", ", " "), "; ", " "), ". ", " "), " ")
    Cells(5, 1).Resize(UBound(a) + 1) = Application.Transpose(a)
End Sub

And, if you answer to this post, don't Quote. Just refer to the Post # if needed.
 
Upvote 0
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.
 
Upvote 0
alansidman, thank very much for the details.

jolivanes, thank you for the code.

All, have a good day.
 
Upvote 0
Another option with a formula
Fluff.xlsm
A
1
2Monday, Tuesday; Wednesday. Thursday Friday
3Monday
4Tuesday
5Wednesday
6Thursday
7Friday
Lists
Cell Formulas
RangeFormula
A3:A7A3=TEXTSPLIT(A2,,{"."," ",",",";"},1)
Dynamic array formulas.
 
Upvote 0
Solution
Fluff, just what i was looking for....works like a dream. Many thanks!!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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