Test to columns help please

    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    Hello

    Please assist with a split text to columns formula

    I seem to be getting lost in the syntax of how to break this sort of text string to columns.

    Best described by looking at the attached xlsx file.


    text split test.xlsx


    I am using Excel 2013


    Thank you

    Elso

    Instead of a formula, use the built in Excel function Text to Columns found on the ribbon.


    External Content www.youtube.com
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.

  • alansidman

    Added the Label Cross Post

    Hi Alan


    Sorry, I was unaware of this cross post rule which https://www.excelguru.ca/forum…t-text-to-columns-formula also notified me of.


    I very rarely turn to message boards or forums and generally not until I totally exhaust other avenues and reading to understand or if I am on a time crunch and spending too much time trying to figure something out.


    Back to the question at hand. The text to column wizard will not work for this application. Looking to write a formula that with perform the text to column as the selections are made.


    Thank you

    Using the example you supplied the Text to Columns works. A power Query using the comma as a delimiter also works.


    Code
    let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> null)),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"})
in
#"Split Column by Delimiter"

    If your actual data is not representative of what you have shown us then I suggest you supply an example of what your actual data looks like so that we can supply you with the easiest possible solution which may or may not include formulas.

    Copy across and down:

    Code
    =TRIM(MID(SUBSTITUTE($A1,", ",REPT(" ",LEN($A1))),(COLUMNS($A$1:A$1)-1)*LEN($A1)+1, LEN($A1)))

    Good luck!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login