Share via

Split by Column not Splitting Into the Correct Number of Columns Automatically but will if I Type the Correct Number of Columns in the Formula Bar in Power Query

Renee Crozier 165 Reputation points
2026-02-25T21:01:02.62+00:00

I have a column of email addresses. There are 1-4 email addresses in each cell separated by a semicolon. In Power Query, when I select the column and then "Split Column by Delimitator", I only get 2 columns instead of 4. In the formula bar, I can type two other columns and get the remaining data.

For example, a cell in the User column has "email1; email2; email3; email4". When I use "Split column by Delimitator", I get User.1 with email1 and then User.2 with email2. The code is Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), {"User.1", "User.2"}). If I add User.3 and User.4, the other two email addresses appear.

I tried splitting into rows and that worked as expected so it's something with the columns. Any ideas?

Microsoft 365 and Office | Excel | For business | Windows
0 comments

Answer accepted by question author

  1. Hendrix-C 15,990 Reputation points Microsoft External Staff Moderator
    2026-02-25T21:37:32.94+00:00

    Hi @Renee Crozier,

    Thank you for posting your question in the Microsoft Q&A forum.

    Based on your sharing, the issue is likely that there is a step that may have configured the Power Query to output only two new columns. For the best approach to identify which one have caused your situation, I will share the steps I took to use Split Columns in Power Query so you can compare and share me which is the setting that have mistakenly set your Query to output only two columns:

    • Based on your sharing, I create an example data like this

    User's image

    • After that, I go to Data tab > Get data > From Table/Ranges
    • In the Query Editor, I select the Email column > Split Column > by Delimiter

    User's image

    • In the new window, I choose delimiter is Semicolon, select "Each occurrence of the delimiter" for Split at. Then open the Advanced options to ensure Columns is selected and number of columns is 4.
    • Note: In my Query Editor, when I change the delimiter the columns number will automatically change based on the maximum column can be split with the delimiter. For example, if I change to Space, the number of columns will automatically change to 11

    User's image

    • Then I press OK to split the columns and the result will be like this

    User's image

    Hope my sharing will help. If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.  

    Thank you for your patience and understanding. Looking forward to your response.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it.      

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

    Was this answer helpful?

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2026-02-27T23:09:52.45+00:00

    Hi,

    This M code will work without having to manually enter

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Headings = List.Transform({1..List.Max(List.Transform(Source[Email], each List.Count(Text.Split(_,"; "))))}, each "Email_"&Number.ToText(_)),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Email", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), Headings)
in
    #"Split Column by Delimiter"

    Hope this helps.

    User's image

    Was this answer helpful?

    0 comments
  2. Q&A Assist
    2026-02-25T21:01:14.71+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    It sounds like you're experiencing an issue with Power Query's automatic column splitting functionality when dealing with multiple email addresses separated by semicolons. In Power Query, when you use the "Split Column by Delimiter" option, it typically creates a new column for each segment based on the delimiter you specify. However, if it only creates two columns instead of four, it might be due to the way Power Query interprets the data or the settings used during the split operation.

    Here are a few suggestions to address this issue:

    1. Check the Delimiter: Ensure that the delimiter you are using is correctly set as ; or ; (with a space), as this can affect how the split is performed.
    2. Advanced Options: When using the split column feature, make sure to select the option to split at "Each occurrence of the delimiter". This should allow Power Query to create as many columns as there are segments in the data.
    3. Manual Entry: As you've noted, manually typing the expected number of columns in the formula bar works. This indicates that the split function is capable of handling the data correctly, but the automatic feature may not be configured to do so in your case.
    4. Using Rows: Since splitting into rows worked as expected, you could consider this as an alternative approach. After splitting into rows, you can then pivot or transform the data as needed to get it back into a columnar format if necessary.

    If the issue persists, it may be beneficial to check for any updates or known issues with Power Query that could be affecting this functionality.

    References:

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.