Comments on: TEXTSPLIT function in Excel: split cells / text strings by delimiter

There may be various situations when you want to split cells in Excel. Now, we finally have a special function for this, TEXTSPLIT, that can separate a string into multiple cells across columns or/and rows based on the parameters that you specify. Continue reading →

Table of contents

Comments page 2. Total comments: 93

  1. Hi Sir,

    Am trying to delimiter the text ending with ; using the textsplit formula for

    Cell A1: DF-P DMA: New York, USA, city,etrt,erter,erterty,erytrhhgf,rtyrthfgh,rtyrtfg,sgdfgdfg,dgfdtry,dgsfgdfgh,sdgfdgfdg,sretsgdf,sdfgsfdg,sfdgsdgfd,sdfgsfdg,sdfvgt,syttrbfbg,sydfghdfgy,styshgfh,sgfhrsthgfh,rtyrthgfdhg,sertygfdhdfgy,sdfysrthfsrth,sertdgdfhdgf,hjghfhjdfg,hjisdfhigh,ioyioahjg,jkaghjs,kjhjkvbjkv,hjkaghj; DF-P Country: city,etrt,erter,erterty,erytrhhgf,rtyrthfgh

    =IFERROR(TEXTSPLIT(A2,";"),"")

    Unfortunately, the texts before ; exceeded 255 hence
    Cell A2 shows empty value
    Cell B2 shows DF-P Country: city,etrt,erter,erterty,erytrhhgf,rtyrthfgh

    Do I need to add max limit in the above formula so the A2 can show
    DF-P DMA: New York, USA, city,etrt,erter,erterty,erytrhhgf,rtyrthfgh,rtyrtfg,sgdfgdfg,dgfdtry,dgsfgdfgh,sdgfdgfdg,sretsgdf,sdfgsfdg,sfdgsdgfd,sdfgsfdg,sdfvgt,syttrbfbg,sydfghdfgy,styshgfh,sgfhrsthgfh,rtyrthgfdhg,sertygfdhdfgy,sdfysrthfsrth,sertdgdfhdgf,hjghfhjdfg,hjisdfhigh,ioyioahjg,jkaghjs,kjhjkvbjkv,hjkaghj

    1. You already asked this question! The text is written in A1. Your formula uses A2. The formula TEXTSPLIT(A1,";") or TEXTSPLIT(A1,";") works correctly

      1. I tried and found that the texts before ; exceed 255 characters and so the Cell A2 shows empty value.

        Is there anything I need to add to the formula =(TEXTSPLIT(A1,";") so that even if the text exceeds 255 character before ; ,the cell will show the data.

        1. I have not added anything to this formula. My formula (TEXTSPLIT(A2,";") shows more than 300 characters in the cell. With REPLACE function, you can insert an additional delimiter at any position if the text is longer than 255 characters.

          =TEXTSPLIT(IF(SEARCH(";",A2)>255,REPLACE(A2,254,0,";"),A2),";")

  2. Hi Sir,
    I'm stuck with an issue on delimiter formula.
    On the below I have a list of texts and I tried to separate them from the text ending with ";"

    Cell A2: DFP-P DMA:New York NY, Minneapolis-St. Paul MN, Austin TX, Houston TX, Phoenix AZ, Chicago IL, Sacramento-Stockton-Modesto CA, Seattle-Tacoma WA, Philadelphia PA, Miami-Ft. Lauderdale FL, Tampa-St. Petersburg (Sarasota) FL, Dallas-Ft. Worth TX, Orlando-Daytona Beach-Melbourne FL, Denver CO, San Francisco-Oakland-San Jose CA, Atlanta GA, Los Angeles CA, West Palm Beach-Ft. Pierce FL, Boston MA-Manchester NH, San Diego CA, Washington DC (Hagerstown MD), Salt Lake City UT, Portland OR;DFP-P Deliver Impressions:Front;DFP-P Device Cat:All Platforms;Audience:1P

    So I used this formula in A3: =IFERROR(TEXTSPLIT(A2,";"),"") but unfortunately
    A3 is showing empty
    B3 shows "DFP-P Deliver Impressions:Front"
    C3 shows "DFP-P Device Cat:All Platforms"
    D3 shows "Audience:1P"

    My query is A3 showed show
    DFP-P DMA:New York NY, Minneapolis-St. Paul MN, Austin TX, Houston TX, Phoenix AZ, Chicago IL, Sacramento-Stockton-Modesto CA, Seattle-Tacoma WA, Philadelphia PA, Miami-Ft. Lauderdale FL, Tampa-St. Petersburg (Sarasota) FL, Dallas-Ft. Worth TX, Orlando-Daytona Beach-Melbourne FL, Denver CO, San Francisco-Oakland-San Jose CA, Atlanta GA, Los Angeles CA, West Palm Beach-Ft. Pierce FL, Boston MA-Manchester NH, San Diego CA, Washington DC (Hagerstown MD), Salt Lake City UT, Portland OR.

    Can you help me with it, I used few formula's from the above but it didn't worked out.

      1. I tried and found that the texts before ; exceed 255 characters and so the Cell A3 shows empty value.

        Is there anything I need to add to the formula =IFERROR(TEXTSPLIT(A2,";"),""), so that even if the text exceeds 255 character before ; ,the cell will show the data.

  3. how do I split text to be in 1 column and numbers in the next, with no delimiter?
    here is an example:
    the568
    hinl658
    thanks123
    a69822
    lot86212

    1. Hi! To extract letters and numbers separately from text, try the custom function RegExpExtract. See this manual for detailed instructions and examples: How to extract substrings in Excel using regular expressions (Regex).
      To extract letters
      =RegExpExtract(A1, "[A-Za-z]", 1)
      To extract numbers
      =RegExpExtract(A1, "\d+", 1)
      I'd recommend you to have a look at our Regex Tools, that are part of Ultimate Suite for Excel, you can find, extract, remove, or replace strings that match a pattern you enter. You can install it in a trial mode and check how it works for free.

  4. Hello

    Please can you assist I am trying to split a string of multiple values into columns

    eg.

    FOREX SUNDRY DEBIT175010458701 ==> FOREX SUNDRY DEBIT 175010458701
    DIGITAL PAYMENT CRABSA BANK SDP00166600 ==> DIGITAL PAYMENT CR ABSA BANK SDP00166600
    ACB CREDITEXP01259 ==> ACB CREDIT EXP01259

    Thank you

  5. Hi, I want to extract each values before space & delimiter is yr,yr,k3,p2,ym. How do I do that. I am familiar with delimiter, but want to learn the formula to do that.
    TEXT STRING
    TX01 INR 80YR TX02 INR 50YR TX03 INR 3184K3
    TX04 INR 236P2 TX05 INR 142YM

    answer required.
    find the below unique delimiter value in cell A1 & A2 get the value/number display before that
    YR 80
    YR 50
    K3 3184
    P2 236
    YM 142
    note: delimiter place is change randomly

    1. Hi, sorry my brain is bleeding so may i ask how i can modify your code so that this string:

      {"success":true,"base":"XAU","timestamp":1671307300,"rates":{"USD":1792.91990274}}

      will only show 1792.91 as a numerical value?

      please note i'm getting this string from webservice function and i'm directly applying the formula to the same cell so won't be using another cell as reference

      1. Hi!
        Split the text with the two delimiters ":" and "}" using the advice from the article above. Then use the CHOOSECOLS function to select the sixth column.

        =CHOOSECOLS(TEXTSPLIT(A1,{":","}"}),6)

        Hope this is what you need.

      1. Dear Sir/Madam,

        Can we execute this formulas in excel 13.

      2. Thank you so much

  6. How would I split this:

    01/23 text text text text
    02/23 text text text
    11/23 text text text

    into three columns?

  7. how can we split number with formula.

    example =1+2+3+4+12+36

    1. Assuming that formula is in A1

      =Textsplit(formulatext(A1),{"=","+"},,True)

  8. Spent hours on this can’t get it to work! All I want is a table that you can enter a full name in column A, press enter and it splits it into separate names in columns B, C & E (1st, middle, surname) Ive got one that splits dates that the whole team can use on sharepoint, so useful. Any tips? Thank you 🙏

  9. Hi, is there a way to make comma-separated values appear in columns as per below, where some values are not present eg:
    original column TEXTSPLIT columns: pig. dog. cat
    pig, dog, cat. 1 1 1
    dog 0 1 0
    cat, pig 1 0 1

    The FALSE to ignore missing values doesn't work here as it sees eg dog as column 1 rather than seeing a missing column 1 and value in column 2 only.
    In this example 1 or 0 could be replaced by {animal name} or {blank}.

    Thanks!

    2. ooh it messed up my text spacing when posting! Hope it still makes sense if you visually move the binary numbers under the 'TEXTSPLIT' column names.

  10. Is there a way to sum up the positive / negative numbers store in a single cell like A1=-32+25-8-16+7+8 by using the textsplit function with two delimeters "+" and "-" and reserve the signs " "+" and "-" for summing up calculation?

  11. I wrote a smal function:
    Function GetSplit(TXT_IN, SEP_IN, FLD_IN1, FLD_IN2)
    ' TXT_IN text input
    ' SEP_IN separator
    ' FLD_IN1 First field to return
    ' FLD_IN2 Number of fields to return

    If FLD_IN1 < 1 Or FLD_IN1 = "" Then
    FLD_1 = 1
    Else
    FLD_1 = FLD_IN1
    End If

    If FLD_IN2 < 1 Or FLD_IN2 = "" Then
    FLD_2 = 1
    Else
    FLD_2 = FLD_IN2
    End If

    TXT_TMP1 = TXT_IN
    POS_1 = 1
    For DUMMY_1 = 1 To FLD_1 - 1
    POS_11 = WorksheetFunction.Find(SEP_IN, TXT_TMP1 & SEP_IN)
    TXT_TMP1 = Mid(TXT_TMP1, POS_11 + 1, 999)
    POS_1 = POS_1 + POS_11

    Next

    POS_2 = WorksheetFunction.Find(SEP_IN, TXT_TMP1 & SEP_IN)
    TXT_TMP1 = Mid(TXT_TMP1, POS_2 + 1, 999)
    For DUMMY_1 = 1 To FLD_2 - 1
    POS_21 = WorksheetFunction.Find(SEP_IN, TXT_TMP1 & SEP_IN)
    TXT_TMP1 = Mid(TXT_TMP1, POS_21 + 1, 999)
    POS_2 = POS_2 + POS_21

    Next

    GetSplit = Mid(TXT_IN, POS_1, POS_2 - 1)

    End Function

    1. hi i try this function in office 365 it's not working office 2010 . how to add this function in office 2010 or earlier version

      =LEFT(_xlfn.TEXTSPLIT(Sheet1!A1,"M31*","*N"),FIND("*",_xlfn.TEXTSPLIT(Sheet1!A1,"M31*","*N"))-1)

  12. I need to know how to delete the last series of a string but that last section length may vary (5 or 6 characters). For example from this string I need to delete the value 1576525: ir158045-17348-1576525 but for this one, I need to delete 173487: ir158034184-173487 or in this one delete 1798867:
    ir1505-151809-154896-1798867

  13. Great tips...I always search your website for excel help.
    Do you have your youtube channel.
    Or are you there in LinkedIn platform....iam sure there will be lot of people who will get to learn from your content. . Please do send the updates if you are there in these platforms. Thank you...

  14. How do I use this in a table? for some reason, it will not work for me with a semicolon or a comma when using refernce text that is part of a table

  15. Please help me...How do i split this? 11)AWARD 9879, xxx Hurricane, Fuj, 16-18 Aug (V 228 1971 227)

    I only want these values to be split:
    9879
    xxx Hurricane
    Fuj
    16-18 Aug
    V 228 1971 227

    please help me

  16. for those like me who yet have Textsplit available, how to split text into array?

    Thanks ahead!

    1. Hello Sunny,

      To split text into multiple columns, you can use older functions that work in all Excel versions. The formulas are slightly more complex, but they do work. For more details, please see the "TEXTSPLIT alternatives" section of this tutorial.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)