• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

extracting value based on "space"

Ateeb Ali

Member
Dear, I am attaching a file, need a formula for this job

I have multiple values (as example can be seen in data column)
I need to arrange it in same sequence with individual value in individual cell
 

Attachments

  • chandoo.xlsx
    9.7 KB · Views: 12
Depending on your Excel version, you may use
=IF(ROW(D1)>(LEN(TEXTJOIN(" ",FALSE,$A$5:$A$14))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,$A$5:$A$14)," ","")))+1,"",INDEX(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(" ",FALSE,$A$5:$A$14)," ";"</b><b>")&"</b></a>";"//b"),ROW(D1)))

EDIT: shorter: =IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(" ",FALSE,$A$5:$A$14)," ";"</b><b>")&"</b></a>";"/a/b["&ROW(D1)&"]"),"")
 

Attachments

  • Copy of chandoo_44262.xlsx
    11.4 KB · Views: 3
Last edited:
Hi @Ateeb Ali,

Easy Way Solution Using Power Query
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Data", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Data.1", "Data.2", "Data.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", Int64.Type}, {"Data.2", Int64.Type}, {"Data.3", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Data.1", type text}, {"Data.2", type text}, {"Data.3", type text}}, "en-IN"),{"Data.1", "Data.2", "Data.3"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Merged", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([Merged] <> null))
in
    #"Filtered Rows"
 

Attachments

  • chandoo.xlsx
    21.8 KB · Views: 3
For reference, a shorter PQ solution

Load Data to PQ UI
Split Column By Delimiter, split in Rows.
Done
67778

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source, {{"Data", type text}}, "nl-BE"), {{"Data", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Data"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data", Int64.Type}})
in
    #"Changed Type"
 
Thanks GraH - Guido
I am sorry but both below formulas not working, you can also check your attached file, when you change figures in cell it all become zero
I need to apply formula to big range;

=IF(ROW(D1)>(LEN(TEXTJOIN(" ",FALSE,$A$5:$A$14))-LEN(SUBSTITUTE(TEXTJOIN(" ",FALSE,$A$5:$A$14)," ","")))+1,"",INDEX(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(" ",FALSE,$A$5:$A$14)," ";"</b><b>")&"</b></a>";"//b"),ROW(D1)))

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(" ",FALSE,$A$5:$A$14)," ";"</b><b>")&"</b></a>";"/a/b["&ROW(D1)&"]"),"

Secondly, I am using MS Excel 2010 SP2, I just installed power query
our second solution of power query split column worked great, thanks a lot
 
Last edited:
Back
Top