Separate values using formula

[Arup Dutta Chowdhury]

I have an excel file where the numbers are kept in one column. I want to separate it into different columns using formula. I have tried with text to column option but the last number is not getting separated.

 

[AlanSidman]

You have an non-printing character after the third comma that is causing the problem. It needs to be cleaned. Here is the Mcode in Power Query to achieve that and then split the data into four columns.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Inserted Cleaned Text" = Table.AddColumn(Source, "Clean", each Text.Clean([Column1]), type text),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Inserted Cleaned Text", "Clean", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Clean.1", "Clean.2", "Clean.3", "Clean.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Clean.1", type text}, {"Clean.2", type text}, {"Clean.3", type text}, {"Clean.4", type text}})
in
    #"Changed Type"

File is attached for your review.

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Clean.1[/td][td=bgcolor:#70AD47]Clean.2[/td][td=bgcolor:#70AD47]Clean.3[/td][td=bgcolor:#70AD47]Clean.4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#E2EFDA]WBD010552,WBD007953,WBD004048,
WBD004456[/td][td=bgcolor:#E2EFDA]WBD010552[/td][td=bgcolor:#E2EFDA]WBD007953[/td][td=bgcolor:#E2EFDA]WBD004048[/td][td=bgcolor:#E2EFDA]WBD004456[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps
M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

 

[pecoflyer]

See attached a formula solution

 

[Arup Dutta Chowdhury]

Is there any other to do it.

 

[AlanSidman]

Pepe, When I open your file, it is absolutely blank. Not even any grid lines.

 

[S. Das]

Hi Arup, if the line break before the last data is unnecessarily inserted then you can use these steps...

1. Open the Find/Replace command window.
2. In the Find what box press and hold the Alt key and then enter “010” from the Numpad portion of your keyboard. (Note: The numbers from the top row of the keyboard will not work.) If “010” does not work, try “013”.
3. Leave the Replace with box empty.
4. Press Replace.
5. After that, you can use Text to Column option to divide the data into 4 columns.

 

[pecoflyer]

Pepe, When I open your file, it is absolutely blank. Not even any grid lines.

Thanks Alan,
strange indeed, I downloaded it from here and everything is OK
I'll retry

Anyone else have the same experience?​

I added a helper column to get rid of the non breaking space char 160 =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
Based on that one extraction starting in column 3 and pulling right : =TRIM(MID(SUBSTITUTE($B1,",",REPT(" ",LEN($B1))), (COLUMN()-3)*LEN($B1)+1, LEN($B1)))

When starting in another column replace column()-3 with column()-nr_of_other_column

 

[Arup Dutta Chowdhury]

This is not working S.das

 

[pecoflyer]

And have you tried my suggestion(s) ?

 

[AlanSidman]

Have you tried the Power Query Solution?