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

The tutorial shows how to use the brand-new TEXTSPLIT function to split strings in Excel 365 by any delimiter that you specify.

There may be various situations when you need to split cells in Excel. In earlier versions, we were already equipped with a number of instruments to accomplish the task such as Text to Columns and Fill Flash. Now, we also 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.

Excel TEXTSPLIT function

The TEXTSPLIT function in Excel splits text strings by a given delimiter across columns or/and rows. The result is a dynamic array that spills into multiple cells automatically.

The function takes as many as 6 arguments, only the first two of which are required.

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

Text (required) - the text to split. Can be supplied as a string or cell reference.

Col_delimiter (required) - a character(s) that indicates where to split the text across columns. If omitted, row_delimiter must be defined.

Row_delimiter (optional) - a character(s) that indicates where to split the text across rows.

Ignore_empty (optional) - specifies whether to ignore empty values or not:

  • FALSE (default) - create empty cells for consecutive delimiters without a value in-between.
  • TRUE - ignore empty values, i.e. don't create empty cells for two or more consecutive delimiters.

Match_mode (optional) - determines case-sensitivity for the delimiter. Enabled by default.

  • 0 (default) - case-sensitive
  • 1 - case-insensitive

Pad_with (optional) - a value to use in place of missing values in two-dimensional arrays. The default is a #N/A error.

For example, to divide a text string in A2 into multiple cells using a comma and a space as the separator, the formula is:

=TEXTSPLIT(A2, ", ") TEXTSPLIT function to divide text string in Excel

TEXTSPLIT availability

The TEXTSPLIT function is only available in Excel for Microsoft 365 (Windows and Mac) and Excel for the web.

Tips:

  • In Excel versions where the TEXTSPLIT function is not available (other than Excel 365), you can use the Text to Columns wizard to split cells.
  • To perform the reverse task, i.e. to join the contents of multiple cells into one using a certain delimiter, TEXTJOIN is the function to use.

Basic TEXTSPLIT formula to split a cell in Excel

For starters, let's see how to use a TEXTSPLIT formula in its simplest form to split a text string by a particular delimiter.

Split a cell horizontally across columns

To divide the contents of a given cell into multiple columns, supply a reference to the cell containing the original string for the first (text) argument and the delimiter that marks the point where the splitting should occur for the second (col_delimiter) argument.

For example, to separate the string in A2 horizontally by comma, the formula is:

=TEXTSPLIT(A2, ",")

For the delimiter, we use a comma enclosed in double quotes (",").

As a result, each item separated by a comma goes into an individual column: Splitting a cell horizontally across columns

Split a cell vertically across rows

To split text across multiple rows, the third argument (row_delimiter) is where you place the delimiter. The second argument (col_delimiter) is omitted in this case.

For instance, to separate the values in A2 into different rows, the formula is:

=TEXTSPLIT(A2, ,",") Splitting a cell vertically across rows

Please note that, in both cases, the formula is only entered in one cell (C2). In neighboring cells, the returned values spill automatically. The resulting array (which is called a spill range) is highlighted with a blue border indicating that everything inside it is calculated by the formula in the upper left cell.

Split text by substring

In many cases, the values in the source string are separated by a sequence of characters, a comma and a space being a typical example. To handle this scenario, use a substring for the delimiter.

For instance, to separate the text in A2 into multiple columns by a comma and a space, use the string ", " for col_delimiter.

=TEXTSPLIT(A2, ", ")

This formula goes to B2, and then you copy it down through as many cells as needed. Split cells by a substring

Split string into columns and rows at once

To split a text string into rows and columns at a time, define both delimiters in your TEXTSPLIT formula.

For example, to split the text string in A2 across columns and rows, we supply:

  • The equal sign ("=") for col_delimiter
  • A comma and a space (", ") for row_delimiter

The complete formula takes this form:

=TEXTSPLIT(A2, "=", ", ")

The result is a 2-D array consisting of 2 columns and 3 rows: Splitting a string into columns and rows at once

Separate cells by multiple delimiters

To handle multiple or inconsistent delimiters in the source string, use an array constant like {"x","y","z"} for the delimiter argument.

In the screenshot below, the text in A2 is delimited by both commas (",") and semicolons (";") with and without spaces. To split the string vertically into rows by all 4 variations of the delimiter, the formula is:

=TEXTSPLIT(A2, , {",",", ",";","; "})

Or, you can include only a comma (",") and semicolon (";") in the array, and then remove extra spaces with the help of the TRIM function:

=TRIM(TEXTSPLIT(A2, , {",",";"}))

Splitting a string by multiple delimiters

Split text ignoring empty values

If the string contains two or more consecutive delimiters without a value between them, you can choose whether to ignore such empty values or not. This behavior is controlled by the fourth ignore_empty parameter, which defaults to FALSE.

By default, the TEXTSPLIT function does not ignore empty values. The default behavior works nicely for structured data as in the below example.

In this sample table, the scores are missing in some strings. The TEXTSPLIT formula with the ignore_empty argument omitted or set to FALSE handles this case perfectly, creating an empty cell for each empty value.

=TEXTSPLIT(A2, ", ")

Or

=TEXTSPLIT(A2, ", ", FALSE)

As a result, all the values appear in the appropriate columns. Split strings creating empty cells for missing values.

In case your strings contain homogeneous data, it may stand to reason to ignore empty values. For this, set the ignore_empty argument to TRUE or 1.

For instance, to divide t the below strings placing each skill in a separate cell without gaps, the formula is:

=TEXTSPLIT(A2, ", ", ,TRUE)

In this case, the missing values between consecutive delimiters are ignored completely: Split text ignoring empty values.

Cell splitting case-sensitive or case-insensitive

To control the case-sensitivity of the delimiter, make use of the fifth argument, match_mode.

By default, match_mode is set to 0, making TEXTSPLIT case-sensitive.

In this example, the numbers are separated by the lowercase "x" and uppercase "X" letters.

The formula with the default case-sensitivity only accepts the lowercase "x" as the delimiter:

=TEXTSPLIT(A2, " x ")

Please pay attention that the delimiter has a space on both sides of the letter " x " in order to prevent leading and trailing spaces in the results. Case-sensitive TEXTSPLIT formula

To turn off case sensitivity, you supply 1 for match_mode to force the TEXTSPLIT formula to ignore the letter case:

=TEXTSPLIT(A2, " x ", , ,1)

Now, all the strings are split correctly by either delimiter: Case-insensitive TEXTSPLIT formula

Pad missing values in 2D array

The last argument of the TEXTSPLIT function, pad_with, comes in handy in case one or more values are missing in the source string. When such a string is split into both columns and rows, by default, Excel returns #N/A errors instead of the missing values not to mangle the structure of a two-dimensional array.

In the below string, there is no "=" (col_delimiter) after "Score". To keep the integrity of the resulting array, TEXTSPLIT outputs #N/A next to "Score". #N/A errors appear in the place of missing values.

To make the result more user-friendly, you can replace the #N/A error with any value you want. Simply, type the desired value in the pad_with argument.

In our case, that could be a hyphen ("-"):

=TEXTSPLIT(A2, "=", ", ", , ,"-") Pad missing values with a hyphen.

Or an empty string (""):

=TEXTSPLIT(A2, "=", ", ", , ,"") Show blanks for missing values.

Now that you've learned practical uses of each argument of the TEXTSPLIT function, let's discuss a couple of advanced examples that can help you cope with non-trivial challenges in your Excel spreadsheets.

Split dates into day, month and year

To divide a date into individual units, first you need to convert date to text because the TEXTSPLIT function deals with text strings while Excel dates are numbers.

The easiest way to convert a numeric value to text is by using the TEXT function. Just be sure to supply an appropriate format code for your date.

In our case, the formula is:

=TEXT(A2, "m/d/yyyy")

The next step is to nest the above function in the 1st argument of TEXTSPLIT and enter the corresponding delimiter for the 2nd or 3rd argument, depending on whether you are splitting across columns or rows. In this example, the date units are delimited with slashes, so we use "/" for the col_delimiter argument:

=TEXTSPLIT(TEXT(A2, "m/d/yyyy"), "/") Splitting dates into day, month and year

Split cells and remove certain characters

Imagine this: you've split a long string into pieces, but the resulting array still contains some unwanted characters, such as parentheses in the screenshot below:

=TEXTSPLIT(A2, " ", "; ") The resulting array contains some unwanted characters.

To strip off the opening and closing parentheses at a time, nest two SUBSTITUTE functions one into another (each replacing one parenthesis with an empty string) and use the TEXTSPLIT formula for the text argument of the inner SUBSTITUTE:

=SUBSTITUTE(SUBSTITUTE(TEXTSPLIT(A2, " ", "; "), "(", ""), ")", "") Split cells removing unwanted characters

Tip. If the final array contains too many extra characters, you can purge them using one of the methods described in this article: How to remove unwanted characters in Excel.

Split strings skipping certain values

Supposing you want to separate the below strings into 4 columns: First name, Last name, Score, and Result. The problem is that some strings contain the title "Mr." or "Ms.", because of which the results are all wrong: Strings are split incorrectly because of excessive values.

The solution is not obvious but quite simple :)

In addition to the existing delimiters, which are a space (" ") and a comma and a space (", "), you include the strings "Mr. " and "Ms. " in the col_delimiter array constant, so that the function uses the titles themselves to separate the text. To ignore empty values, you set the ignore_empty argument to TRUE.

=TEXTSPLIT(A2, {" ",", ","Mr. ","Ms. "}, ,TRUE)

Now, the results are absolutely perfect! Split strings skipping certain values.

TEXTSPLIT alternatives

In Excel versions where the TEXTSPLIT function is not supported, you can divide strings by using different combinations of the SEARCH / FIND function with LEFT, RIGHT and MID. In particular:

  • Case-insensitive SEARCH or case-sensitive FIND determines the position of the delimiter within a string, and
  • The LEFT, RIGHT, and MID functions extract a substring before, after or between two instances of the delimiter.

In our case, to split the values separated by a comma and a space, the formulas go as follows.

To extract the name:

=LEFT(A2, SEARCH(",", A2, 1) -1)

To pull the score:

=MID(A2, SEARCH(",", A2) + 2, SEARCH(",", A2, SEARCH(",",A2)+1) - SEARCH(",", A2) - 2)

To get the result:

=RIGHT(A2, LEN(A2) - SEARCH(",",  A2, SEARCH(",",  A2) + 1)-1)

For the detailed explanation of the formulas' logic, see How to split strings by character or mask.

Please keep in mind that unlike the dynamic array TEXTSPLIT function, these formulas follow the traditional one-formula-one-cell approach. You enter the formula in the first cell, and then drag it down the column to copy to the below cells.

The screenshot below shows the results: TEXTSPLIT alternatives to split strings in Excel

That's how to split cells in Excel 365 by using TEXTSPLIT or alternative solutions in earlier versions. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

TEXTSPLIT function to split strings – formula examples (.xlsx file)

100 comments

  1. If you want to apply TEXTSPLIT to an array*, it doesn't seem to work with array formulas, so you want nest a TEXTJOIN inside of it.

    *why not just use a separate TEXTSPLIT formula on each row? Maybe because this is part of a bigger formula (like looking for the maximum or something).

    For example, if I want to take a range of dates (Col A) in the "M/D/YYYY" format and break it up into 3 columns (for M, D, and YYYY), I first join all the rows, delimited by some symbol I know won't be found in that text:
    =TEXTSPLIT(TEXTJOIN("♥",FALSE,A:A),"/","♥")

    This also allows you to replace "A:A" with something like a FILTER()

  2. Please help me to split below numbers separated from text

    Enoch Al Ahsa Station Juba 6018

  3. Hi, can you please help me with this string from a phone bill. After I converted a pdf file to excel, all of the columns are combine, text are separated by space. I am struggling to run text by column. I have used: Textsplit(A1,"","","","","","","") and Textsplit(A1,Char(3),"", Char(8))). But I failed.
    String: 6:55 PM IN (123)456-789 Incoming F 1 -
    I want to split them by columns: Time, Day/Night, Contact Type, To number, Description, Type, Min, Cost

    My expectation should be:
    Time: 6:55
    Day/Night: PM
    Contact Type: IN
    To number: (123)456-789
    Description: Incoming
    Type: F
    Min: 1
    Cost: -

    Hope to get your help.
    Thank you so much

    1. Hi! Please re-check the article above since it covers your case. If my understanding is correct, to split text by columns using a space as a separator, use the formula:

      =TEXTSPLIT(A1," ")

  4. i WANT TO SEPERATE NAMES AND DOB AND SSN

    Aiden Alley, 765-34-4226, 09/17/2005
    Erik Alley, 764-29-9531, 06/12/2016
    Tanna Alley, 764-35-7608, 10/22/2018
    Mirah Alle, 764-96-7235, 10/30/2017
    Nora Alley, 632-82-6653, 04/01/2022
    Ned Alley, 199-41-8632, 02/10/2013

    EXAMPLE: First Last dob ssn
    in four colums

    1. Hi! Unfortunately, from the description of your problem, I have no idea what kind of result you had in mind. You can split the text using the TEXTSPLIT function, and then select the desired values using the CHOOSECOLS function. For example:

      =CHOOSECOLS(TEXTSPLIT(A1," "),1,3)

  5. I am trying to split text that is in one cell into multiple columns, but the text is not the same length or where a break should be. This is a list of Artist - Song Titles - Number of plays

    2 1 JACK WHITE That's How I'm Feeling 2656 2347 309 3.589
    1 2 LINKIN PARK The Emptiness Machine 2536 2690 -154 4.463
    3 3 SUM 41 Dopamine 2229 2174 55 2.777
    6 4 DEXTER AND THE MOONROCKS Sad In Carolina 1925 1758 167 2.267

    Ultimately I am hoping for one formula that I can copy for each line (between 50-100 lines)
    I know how to take out the numbers on the left (ex: 2 1).
    So, the final result would like this: "--" WOULD BE where I want each column to have the separate entries
    Each line may have a different number of characters or words

    JACK WHITE--That's How I'm Feeling--2656
    LINKIN PARK--The Emptiness Machine--2536
    SUM 41--Dopamine--2229
    DEXTER AND THE MOONROCKS--Sad In California--1925

    Hope I explained this so you can understand.
    Thank you, Richard

    1. Hello Richard!
      Your text does not have a common pattern. Therefore, it is impossible to specify exactly which characters should be used to split it into separate columns.

  6. Hi, is it possible to use TEXTSPLIT to separate several text chains in a cell, but placing it in several non-continuous columns?
    e.g. This is in one cell: 6"-HY-HH-120106-BA3W-NT and I need to put it 6" in column A, HY in column D, HH in column G... And so on...

    Thanks for your help!

    1. Hello Federico! You can split text into columns using TEXTSPLIT function as described in the article above. To extract the desired substring into a cell, use CHOOSECOLS function. For example, to get text “HY” try the formula:

      =CHOOSECOLS(TEXTSPLIT(A1,"-"),2)

      1. Thanks Alexander!!! It worked for me.
        I didn't know about those CHOOSE functions.
        I learned something new today.

  7. Hi I kindly request for your assistance. This is my data scanned from a medicine. This is from a QR code and it comes out as 1 line. I need to separate the batch number, expiry, manufacture date and serial.

    010628108600235310130097|172402022100000000015S1X

    Here's what I want as a result

    130097 02/2021 02/2024 00000000015S1X

      1. Thank you very much

  8. Hi,
    I thought I had this figured out but I am running to an issue with =textsplit
    When I cut and paste from the page the textsplit is referencing I get the #REF error. Not cutting and pasting is not an option for me, so I was hoping to create a button that I can click and that would "reset" the textsplit criteria and replace the broken formula. This might require VBA.
    example
    =textsplit(wednesday!A10, ", ")
    down to (wednesday!A1380)
    For my worksheet purpose the button would reference column CL2765 through CL4135 and reset the textsplit criteria referencing Wednesday!A10:A1380
    Any insight or other work arounds would be much appreciated.

      1. Hi, I was able to figure it out using =error.type, then using helper cells!

      2. HI Alexander, thanks for your reply.
        Is this even doable without VBA, just like a macro? Is it even doable with VBA?

        There is another way to get around this but I am running into the issue of the "#REF!" error in the formula. Is there a way for excel to recognize "#REF!" in the formula and not just the cell value.
        I've tried to count, find, search for "#REF!" but it only returns a value if the cell has the error and not embedded in the formula. I hope all of this makes since.
        This is my formula, and I would like a corresponding cell to recognize the "#REF!" and return a value like 1.
        =IFERROR(TEXTSPLIT(MONDAY!#REF!, ", "),"")
        Thanks

        1. Hi! If my understanding of your question is correct, you can use the FORMILATEXT function to get the text of the formula. Then check whether this text contains "#REF!.
          For example:

          =IF(ISNUMBER(SEARCH("#REF!",FORMULATEXT(A2))), "#REF!", "")

          For more information, please read: How to find substring in Excel

  9. How can you split and count the values if the strings are like this:
    A-1-B-1-C-2-D-3-E-1-F-5
    A-2-C-3-D-2-F1
    B-2-D-3-A-1-E-4

    Result:
    A 4
    B 3
    C 5
    D 8
    E 5
    F 6

      1. Jaime is looking to list out the unique letters (A-F) is Column A and show the sum of the numbers associated to those letters in column B.

        1. Hi! TEXTJOIN function joins all values into one text string.
          TEXTSPLIT function will split this string into separate values using the '-' separator.
          WRAPROWS function arranges these values in two columns, with each letter in the first column corresponding to a number in the second column.
          CHOOSECOLS function allows you to select either the column of letters or the column of numbers.
          The letter column is checked by a condition, e.g. "A", and returns TRUE (1) or FALSE (0).
          Multiply this condition by the corresponding numbers and find their sum using SUMPRODUCT function.
          Based on the information provided, the formula could look like this:

          =SUMPRODUCT(CHOOSECOLS(WRAPROWS(TEXTSPLIT(TEXTJOIN("-",TRUE,A1:A3),"-"),2),2) * (CHOOSECOLS(WRAPROWS(TEXTSPLIT(TEXTJOIN("-",TRUE,A1:A3),"-"),2),1)="A"))

  10. Hi Alexander,

    I have a long list of comma-separated Microsoft products and have been trying to figure out a formula that can parse out the text (preserving spaces placement) to end up with a single list of the products. Once this is figured out, I want to wrap that in the UNIQUE and SORT functions resulting in a list with one instance of each product as there are many duplicates. Here are a few lines from the data I'm working with...

    Microsoft Power Automate Free,Azure Active Directory Premium P1
    Microsoft Power Automate Free,Azure Active Directory Premium P1,Office 365 E3
    Microsoft Power Automate Free,Azure Active Directory Premium P1,Office 365 E3
    Microsoft Power Automate Free,Azure Active Directory Premium P1,Office 365 E3
    Microsoft Power Automate Free,Azure Active Directory Premium P1,Office 365 E3,Teams Exploratory Trial
    Microsoft Power Automate Free,Microsoft 365 Audio Conferencing,Office 365 E3
    Microsoft Power Automate Free,Microsoft 365 Audio Conferencing,Power BI (free),Office 365 E3
    Microsoft Power Automate Free,Office 365 E3

    Sure hoping you can help. I've spent way too much time trying to figure this out on my own (including asking Chat GPT and Bard!) to no avail. Thank you!! :)

    1. Hi! I don't know what kind of result you want, or what your definition of a duplicate is. Use the TEXTSPLIT function with a comma as the separator to split the text into columns. Then use the TEXTJOIN function to join the columns you want. Identify duplicates as recommended here: How to remove duplicates in Excel (duplicate rows, values and partial matches). I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.

  11. Hi
    I have multi line Text in a cell with inconsistent delimiters.
    I'd like to split to rows using the line break delimiter.
    I can see i can use the "other" delimiter in Text to Columns using Alt0010 to specify the line delimiter but I can not find a solution to split into Rows ( rather than columns )
    Must I split to Columns and then transpose ?

    I can select Text ( Double Click and select text in Cell ) and then paste and it pastes to rows but this is very manual.
    Hoping there is a way thanks

      1. Hi
        I tried using this textsplit formula exactly the same with your example above... but the result is "#NAME?".

        Cell A2 : Aiden, 93, Excellent
        Cell B2 : =TEXTSPLIT(A2; ", ")
        Result in cell B2 : #NAME?
        * My excel formula setting uses semicolon (not a comma)

        Plz kindly explain ..

  12. 23/10/2020 23:55:25 How should I Split this?

    1. You haven't specified how you want to split, or what character to use as a separator. If you have text in your cell, all the formulas you need are in the article above. Or ask your question in a way that can be answered.

  13. SOS :) I like to troubleshoot, but not knowing Excel as well and after a couple days, I'm waving my flag. It's a very old directory that I'm cleaning up. Can anyone help with this? (Hope I explain well!)

    I have a column with different delimiters (imported as below). I've managed to separate some names that do not contain parentheses or the title of Mr & Mrs, but the names that imported as below are tough. (Some names have a Mr. & Mrs at the end or a M/M) I'm not sure if I need TRUE? And, I have a blank row in between each line item, so not sure how to omit the formula from being applied. Thanks for any help! :)

    Example: Alekna,Michael (Christin),Mr & Mrs

    Trying to separate and remove the parentheses and include the title Mr & Mrs in my "fourth" column.
    Here's my formula attempt:

    =TEXTSPLIT(A13, {" ",",","Mr. & Mrs.","M/M"},,TRUE)

      1. Hi Alexander! Many thanks for your reply! Very appreciative! I'll give it a shot :)

  14. trying to split 14-14E208 and I get "4_14 1.40E+209"
    I know if I have a ' in front of 14E208 it keeps it text but I have a big sheet I need to apply this to.
    Help please

      1. Cell E3 containing "14-14E208" if you try to split the 14 from 14E208 I get the error.
        Formula : "=SPLIT(E3,"-")"

        1. The SPLIT function does not exist. If I use the formula =TEXTSPLIT(E3,"-"), 14E208 is extracted as text. I don't get an error.

  15. Hi Alex, thanks for this guide it has saved me so much head scratching.
    I don't know if you would have the answer, but do you have any idea how I would add the textsplit formula (with it's dynamic population of columns filled, based on the split cell) using VBA?

    I currently have a sheet that is updated periodically to have a variable data set and so am trying to future proof and automate as much as possible.

    I have tried the following code, but to no avail, it only duplicates the first part of the split (before the delimiter) into the adjacent cell, but goes no further:

    Sub Q_IP_Service_Split()

    Q_IP_Service_Split Macro

    Sheets("Import Prepared").Select
    Range("AS4").Select
    ActiveCell.FormulaR1C1 = _
    "=TEXTSPLIT(RC[-1],"";"")"
    Range("AS4").Select
    Selection.AutoFill Destination:=Range("AS4:AS" & Range("E" & Rows.Count).End(xlUp).Row)
    Range("AS4:AS" & Range("E" & Rows.Count).End(xlUp).Row).Select
    End Sub

    Thanks

      1. All good, appreciate you letting me know. Thanks again for the guide

  16. I have a few hundred rows and each row has a column with email-addresses. Both internal and external addresses. The internals are not interesting, only the external ones.
    Suppose I have the following email-address: name@ company.com and the formula should result in company. What would be the correct formula.
    I have been struggling a bit with the textsplit function but can't get in to work.

    1. Hi! To extract a domain from an email address, you can use regular expressions. You can find the examples and detailed instructions here: How to extract substrings in Excel using regular expressions (Regex). Here is an example formula:

      =REPLACE(RegExpExtract(A1, "@([A-Za-z0-9\.\-]+\.[A-Za-z]{2,24})"), 1, 1, "")

      I recommend paying attention to the Regex tool. You can find, extract, compare, delete, or replace strings that match the regular expression pattern you enter. You don't need to install any VBA code. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

  17. Hello
    I want split this text : A1={a b c ; e , f g h I }
    Result:
    A2= {a b}, B2= {b},C2= {f g},D2= {g h},E2= {e}
    Please solve my essue.

    1. Hi! Remove all unnecessary characters from the text using these instructions: How to delete special / unwanted characters in Excel

      TEXTSPLIT(TRIM(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(A1,",",""),";",""),"}",""),"{",""))," ")

      All your results have a different pattern. So select the letters you want using the CHOOSECOLS function. For example, {a b} :

      ="{"&TEXTJOIN(" ",, CHOOSECOLS(TEXTSPLIT(TRIM(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(A1,",",""),";",""),"}",""),"{",""))," "),1,2))&"}"

  18. Hi @Alexander Trifuntov,

    Can you please help me the below scenario. Column1 always contains single value and ColumnB contains associated values with some delimiter. Table should be flattened for each 1-n relation. Numbers of rows are dynamic.

    Code SubCode
    A AB|AC
    B BA|BB|BC
    C CA
    D DA|DB|DC|DD|DE

    Now the output should be
    Code SubCode
    A AB
    A AC
    B BA
    B BB
    B BC
    C CA
    D DA
    D DB
    D DC
    D DD
    D DE

    Thanks in advance.

  19. Hello!

    I am hoping to use a formula to convert the following delimited data example:

    [Column A] [Column B]
    Primary 1 Secondary 1;Secondary 2;Secondary 3
    Primary 2 Secondary 1;Secondary 2
    Primary 3 Secondary 1;Secondary 2;Secondary 3;Secondary 4

    into this:

    [Column A]
    Primary 1
    Secondary 1
    Secondary 2
    Secondary 3
    Primary 2
    Secondary 1
    Secondary 2
    Primary 3
    Secondary 1
    Secondary 2
    Secondary 3
    Secondary 4

    Could I use textsplit to do this? Thanks in advance!

    2. To be clear, in the original data format, column A contains the primary entities, and column B contains a delimited list of the secondary entities.

  20. Hello, I am wondering if there is a simple way using either TEXTSPLIT or Convert to Table to split text from a cell based on the last word/number in the cell.
    I have data that has usually a number (with 2 decimal places i.e. ***.**) at the end of the text in the cell. I am trying to find a way to split the data such that the cell is split into two based on the last text item i.e number. The only way I can figure out how to do this which is very tedious for large datasets, is to ADD a delimiter in front of the last text item (number) for each cell and then use the delimiter method with Convert to Table to split the cells. I have given a fake example of data below that is similar, as you can see the preceding text is variable in that it is number and text and various lengths, and the final text in the cell is a number:

    Number of houses built in 2019 458.00
    Insurance Payment May 2022 151.46
    Item#XYZ6785ERYT for contractor 1096.43
    Hours billed for labour 3098.75
    Bicycle racks 43.00
    Credit 0.99

    1. Hello! To extract a number with two decimal digits at the end of a text string, you can use regular expressions. Here are instructions with examples for you: How to extract substrings in Excel using regular expressions (Regex). For example:

      =RegExpExtract(A1, "(\s[0-9]+\.[0-9]{2})\b")

      I recommend paying attention to the Regex Tools. In this case, you do not need to install the VBA code. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

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 :)