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

Separate values using formula

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.
 

Attachments

  • Book4.xlsx
    9.1 KB · Views: 6
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.

Code:
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
A
B
C
D
E
1
Column1Clean.1Clean.2Clean.3Clean.4
2
WBD010552,WBD007953,WBD004048,
WBD004456
WBD010552WBD007953WBD004048WBD004456
Sheet: Sheet3

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
 

Attachments

  • Book4 (1).xlsx
    9.1 KB · Views: 3
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.
 
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 :rolleyes:
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
 

Attachments

  • Copy of Book4(2).xlsx
    9.7 KB · Views: 3
Back
Top