imported comma seperated data

[Kiwi den]

I have a sheet that i want to import data to (sheet1 B2) this data is seperated by a comma i want to seperate and create it listed in a single column on (sheet 2 B2, B3, B4 etc)
can someone please help with a formula
thanks in advance

 

[alansidman]

Here is the Mcode for a Power Query Solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Value[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"] Bob[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"] Carl[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"] Kevin[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"] Bill[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"] Olly[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]7[/td]
[td="bgcolor:#FFFFFF, align:CENTER"] Steve[/td]
[/tr]
[/table]

 

[Kiwi den]

Hi have tried formatting cell as general, with no luck

 

[Peter_SSs]

have tried formatting cell as general, with no luck

That was only part of the suggestion. Did you do the final part too (with that same formula cell still selected)?

Select A2 and format it to General then press F2 followed by Enter to re-confirm the formula & then copy down.

If formulas are still displayed after that then perhaps you have this worksheet set to display formulas rather than their results. Exact instructions may differ for different Excel versions but try following these steps
File ribbon tab -> Options -> Advanced -> Scroll down to 'Display options for this worksheet and ensure 'Show formulas in cells instead of their calculated results' is not checked. (Keyboard shortcut for toggling this setting is Ctrl+`

 

[Kiwi den]

Have tried this with no avail... it keeps coming up with error with the section A$1, =iferror(trim(mid(substitute(substituteA$1"",""),",",rept("",100)),rows(a$2:a2)*100-99,100)),"")

 

[Peter_SSs]

- Did you originally copy and paste my formula from the forum or did you re-type it yourself? Re-typing often results in errors.

- Can you confirm that with your version of Excel, the normal separator in formulas is a comma (,) not a semi-colon (?

If still the problem, can you make a simple dummy file that has this problem and put the formula into A2 with an "x" before the = sign (so Excel stops trying to interpret it as a formula) and upload that file to Dropbox/OneDrive/Google Drive or similar, share it & and provide us with a link?

 

[Kiwi den]

I copied and paste the formula
the version of excel is Office professional plus 2016

 

[Peter_SSs]

I copied and paste the formula
the version of excel is Office professional plus 2016

So that still leaves ..

- Can you confirm that with your version of Excel, the normal separator in formulas is a comma (,) not a semi-colon (?

If still the problem, can you make a simple dummy file that has this problem and put the formula into A2 with an "x" before the = sign (so Excel stops trying to interpret it as a formula) and upload that file to Dropbox/OneDrive/Google Drive or similar, share it & and provide us with a link?

 

[Kiwi den]

NORMAL SEPERATOR IS A ,
I will provide link asap

 

[Peter_SSs]

I will provide link asap

OK.
In the interim, if you have not already done so, try setting up a brand new workbook with just that one example as in post 7 and see if you get the same problem in a fresh workbook.

 

[Kiwi den]

https://www.dropbox.com/preview/dummy file for test.xlsx?role=personal