Extract Data From Single Cell [merged] to Multiple Column

[muhammad susanto]

hi all.

how to extract data from single cell (merged cell) like this:

Book1
	A	B	C	D	E
1	data	extract
2	Mr XXX 198505162008121003 New York, 16-05-1985	Mr XXX	198505162008121003	New York	16/05/1985
3
4
5
6
7	Mrs. YYYY 197404231996021001 Hongkong, 23-04-1974	Mrs. YYYY	197404231996021001	Hongkong	23/04/1974
8
9
10
11
12	Mr ZZZZZZZZZ 198301282009012003 France, 28-01-1983	etc…...
13
14
Sheet1

thanks in advance,

 

[arthurbr]

Perhaps use Power Query after selecting your range and making it an XL table called "Table"

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([data] <> null)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "data", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"data.1", "data.2", "data.3"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "data.3", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"data.3.1", "data.3.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"data.3.2", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"data.1", "Name"}, {"data.2", "Number"}, {"data.3.1", "Place"}, {"data.3.2", "Date"}})
in
    #"Renamed Columns"

 

[muhammad susanto]

hi arthurbr, thank you but i'm not familiar using Power Query. Have you another idea/solution?

 

[Kaper]

Try (for sure in excel 365, I'm not sure if it's good in 2021 too)) in B2:

=IF(A2<>"",TEXTSPLIT(A2,CHAR(10)),"")

and copy down

Or just a single formula in B2 as shown on this XL2BB snippet:

Book1
	A	B	C	D	E
1	data	extract
2	Mr XXX 198505162008121003 New York, 16-05-1985	Mr XXX	198505162008121003	New York, 16-05-1985
3
4
5
6
7	Mrs. YYYY 197404231996021001 Hongkong, 23-04-1974	Mr XXX	198505162008121003	New York, 16-05-1985
8
9
10
11
12	Mr ZZZZZZZZZ 198301282009012003 France, 28-01-1983	Mr XXX	198505162008121003	New York, 16-05-1985
13
Sheet2

Cell Formulas
Range	Formula
B2:D12	B2	=IF(A2:A12<>"",TEXTSPLIT(A2,CHAR(10)),"")
Dynamic array formulas.

 

[arthurbr]

=IF(A2<>"",TEXTSPLIT(A2,CHAR(10)),"") does not split the place and date...
Try

=IF(A2<>"",TRIM(TEXTSPLIT(SUBSTITUTE(A2,CHAR(10),"%");{"%",","}),"")

You can replace % with any string you are sure will never appear

 

[alansidman]

FYI
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.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

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

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

 

[Kaper]

Going back to formulas - Indeed, I was glad that I noticed it is CHAR(10) and I missed the second part.

So my corrected proposition is either a longer:

=IF(A2:A12<>"",TEXTSPLIT(A2,MAKEARRAY(1,2,LAMBDA(r,c,CHAR(34*c-24)))),"")

or shorter one:

=IF(A2:A12<>"",TEXTSPLIT(SUBSTITUTE(A2,CHAR(10),","),","),"")

 

[Peter_SSs]

@Kaper
I wonder if you tested either of those post #7 suggestions with the sample data &/or looked closely at the results?
Also check the mini sheet results in post #4

@arthurbr
You do not have matching parentheses and also have a mix of comma and semicolon formula separators.
It would also be simpler to substitute the CHAR(10) with a comma (as @Kaper has tried) as you then only need one TEXTSPLIT delimiter instead of two.

So, my combination suggestion is this.

25 02 18.xlsm
	A	B	C	D	E
1	data
2	Mr XXX 198505162008121003 New York, 16-05-1985	Mr XXX	198505162008121003	New York	16-05-1985
3
4
5
6
7	Mrs. YYYY 197404231996021001 Hongkong, 23-04-1974	Mrs. YYYY	197404231996021001	Hongkong	23-04-1974
8
9
10
11
12	Mr ZZZZZZZZZ 198301282009012003 France, 28-01-1983	Mr ZZZZZZZZZ	198301282009012003	France	28-01-1983
13
14
Split

Cell Formulas
Range	Formula
B2:E2,B12:E12,B7:E7,B3:B6,B8:B11	B2	=IF(A2<>"",TRIM(TEXTSPLIT(SUBSTITUTE(A2,CHAR(10),","),",")),"")
Dynamic array formulas.

 

[Kaper]

@Peter_SSs

Indeed, I shall clear my glases
Formulas in #7 shall be based on single cell (not a range) and then copied.

Sorry for that mess. I'm going to brew a coffe for myself. Or may be better, I'd buy one ready made

 

[Peter_SSs]

Sorry for that mess. I'm going to brew a coffe for myself. Or may be better, I'd buy one ready made

Cheers, good plan.

BTW, the result can be obtained with dual delimiters without the SUBSTITUTE if the Alt+Enter is actually typed into the formula between double quote marks like this instead of using the CHAR() function.

25 02 18.xlsm
	A	B	C	D	E
1	data
2	Mr XXX 198505162008121003 New York, 16-05-1985	Mr XXX	198505162008121003	New York	16-05-1985
3
4
5
6
7	Mrs. YYYY 197404231996021001 Hongkong, 23-04-1974	Mrs. YYYY	197404231996021001	Hongkong	23-04-1974
8
9
10
11
12	Mr ZZZZZZZZZ 198301282009012003 France, 28-01-1983	Mr ZZZZZZZZZ	198301282009012003	France	28-01-1983
13
14
Split (2)

Cell Formulas
Range	Formula
B2:E2,B12:E12,B7:E7,B3:B6,B8:B11	B2	=IF(A2<>"",TRIM(TEXTSPLIT(A2,{" ",","})),"")
Dynamic array formulas.