Comments on: How to split text string in Excel by comma, space, character or mask

Splitting text from one cell into several cells is the task all Excel users are dealing with once in a while. Today, we are going to take an in-depth look at how you can break strings into different elements using formulas and the Split Text feature. Continue reading →

Comments page 2. Total comments: 306

  1. One suggestion: the example with the "To extract the color ..." part using MID and Search would be improved if the separator is not a hyphen in both instances. It is hard to tell which reference is to the first instance of a hyphen and which is to the 2nd instance...unless one already knows. Otherwise a really great resource. Thank you!

  2. Hello - Alexander seems to be a great help here with specialized cell date. Alexander - maybe you could help me here?

    I have a cell that can have multiple text values in it split on multiple lines in the cell H:

    "Shift Differential Rate 2, 1.50 USD Hourly
    Shift Differential Rate 3, 2.00 USD Hourly
    Shift Differential Rate 4, 2.00 USD Hourly
    Hourly - Hourly Plan, 15.29 USD Hourly"

    I want to split the string info I, J, K, L

    Additionally there are cells that do not have this text but only this text in Cell H:
    Salary - Salary Plan, 64,480.00 USD Annual

    Any suggestions.
    Thx in advance

  3. Hello, I have a long text in one column and I need to split it into different sections. When I tried to split by all "," "." " " I have over 5000 columns. Anyone who might help ?
    Part of the text in one column:
    Zsolt Geretovszky University of Szeged, Hungary,2,33,16.50.Ziming Zhong 1,56,56.00,Giorgio Maddaluno.Zhixian X Jiu Wuhan Polytechnic University, China,2,4,2.00.

    Wanted result: (bigger spaces = different columns - f.e. Zsolt Geretovszky is name, university of szeged is school etc.)
    Zsolt Geretovszky University of Szeged Hungary 2 33 16.50
    Ziming Zhong 1 56 56.00
    Giorgio Maddaluno
    Zhixian X Jiu Wuhan Polytechnic University China 2 4 2.00

    1. Hello!
      Your data doesn't have a pattern. Space is used in the first and last name, and between the first name and the school. The period is used in numbers and to separate records. I cannot help you.

  4. my data in one column:

    GLES2_IMPL_EXPORT extern const char kEnableGpuClientTracing[]
    GPU_EXPORT extern const char kDisableShaderNameHashing[]
    extern const char kNearbyShareHTTPHost[]
    COMPONENT_EXPORT(ASH_CONSTANTS) extern const char kArcDisableAppSync[]

    And i need:- kEnableGpuClientTracing[]
    kDisableShaderNameHashing[]
    kNearbyShareHTTPHost[]
    kArcDisableAppSync[] to be seperated.

    When I'm using the formulae it got separated with "extern const char kDisable....."
    could you help me through this?

  5. I have a comma delimited list of zip codes that I need split into 1200 character max lengths while still keeping the zip codes together, but not going over 1200 characters. Is there any formula that will do that so I don't have to manually do it?

    Thanks!

    2. Just to clarify - they are all in one cell and it is 8747 characters in one cell. I'd like to have cells that are no more than 1200 characters in length, without breaking apart the zip codes. Thanks!

  6. Hi,
    I would like to split an 11 digits sequence e.g. 23456543987 into 11 separate cells in excel.
    Can you please advise me?
    thanks a lot.

    1. Hello!
      You can use the formula:

      =MID($A$1,ROW(A1),1)

      After that, you can copy this formula down along the column.

      1. thank you Alexander

  7. Go to Data > Text to Columns and set delimiter to -

  8. Hi,
    In cells of Column A I have data:
    55771 13 units
    44231 5 units
    12341 22 units
    and etc....
    In adjacent cells of Column B I need:
    55771
    44231
    12341
    In adjacent cells of Column C:
    13 units
    5 units
    22 units
    Is it possible with formula?

    1. Hello!
      The formula below will do the trick for you:

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

      and

      =MID(A1,SEARCH(" ",A1,1)+1,50)

      1. Is there any chance to do it with one formula?

        1. Hello!
          The formula changes the value only in the cell in which it is written. You have 2 columns, so you need 2 formulas. Or write a VBA macro.

          1. Ok, Thank you

  9. Hi, please help me out.

    Column A has,
    7000
    12345
    54543
    7890

    it has to be separated into two units from the last and to be written in columns B, C and D... like...

    In Column B
    00
    45
    43
    90

    In column C
    70
    23
    45
    78

    In column D
    0
    1
    5
    0

    (zero- since there are no more digits there)

    Please help me with the formula.
    Thanks.

    1. Hello!
      Use formulas

      =MID(A2,LEN(A2)-1,2)

      =MID(A2,LEN(A2)-3,2)

      =IFERROR(MID(A2,LEN(A2)-4,1),0)

      You can learn more about MID function in Excel in this article on our blog.

  10. Cell has string of 6 digit numbers: A1=123456 234567 345678 456789 ... Split the string of 6-digit numbers into single column, B1=123456, B2=234567, B3=345678, B4=456789, .... Any suggested solution? Thank you.

    1. Try using:
      =MID($A$1,IF(ROW(A1)>1,(ROW(A1)-1)*6+1,ROW(A1)),6)
      This will read the row the formula is on and give you the 6 characters from that row's set
      Drag the formula down for as many rows as there are data sets in cell A1

    2. Hello!
      You can use the Split Text tool as described in the article above. Then you can write the obtained results into a column using the Transpose Range tool.
      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.

  11. Hi, My query is that if we have first names and last names combined in a column (with both of them starting with capital letters) then is it possible to separate the strings into first name and last name? both the first and last names have different number of characters.

      1. Thank You!!

  12. 012-01-I-020-RUBBER RUNNER SET

    I WANT TO SPLIT ABOVE DATA AS BELOW
    012-01-I-020 IN ONE CELL AND RUBBER RUNNER SET IN ANOTHER CELL

    my data actual data as below which i want to split.
    012-01-I-020-RUBBER RUNNER SET
    01-U-428-UNWINDER UN SCREW AND FLANGE ASSY M104
    02-P-458-FR 59-48 1080 28 1334 MS/MS 104
    03-C-096-CUTTING BRIDGE ASM FOR Ø130ROTARY(M-104)
    03-C-4016-GFR 44-32 360 25 386 MS/MS 104
    09-01-S-142-TOP & BOTTOM KNIFE BRKT-L.H 60MM WIDTH

  13. I'm looking to split Name, address, city, state, zip in their own columns. As of now, they are in 1 column

    Name Address City, State Zip

    Thank you for any help!!

  14. HI, id really love your help on this

    I'm trying to split the below into columns, I'm doing ok with the =LEFT formula but trying to split the rest is proving difficult

    WIFI:T:WPA;S:RUT240_93AD;P:k2T6Vvu5;;SN:1107473141;I:861585044986089;M:001E422B93AB;B:081;

    For example, anything after the ":" is what im trying to get.
    Eg - values ONLY after S: RUT240_93AD, P: k2t6Vvu5, SN: 1107473141

    I would like just the model and serial numbers in their own column, without the "S:" attached to it

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =MID(A1,SEARCH("S:",A1,1)+2,SEARCH("I:",A1,SEARCH("S:",A1,1))-3-SEARCH("S:",A1,1))

      I hope my advice will help you solve your task.

  15. Hi,

    I have a scenario where I get string followed by numbers as below in a cell. Sometimes it won't be in sequential order. I am able to split it taking ";" as delimiter. But not able to sort and find min value, max value as it is a string followed by number.

    Integration Sprint 34;Integration Sprint 35;Integration Sprint 36;Integration Sprint 37;Integration Sprint 38;Integration Sprint 39;Integration Sprint 40

    I need to split it considering ";" as delimiter and sort it in sequential order.
    Then i need to find minimum value and max value

    Could you please suggest excel tips for it

    1. Hi!
      We have tools that can solve your task in a couple of clicks - Split Tool and Extract tool. You can split text into rows with semicolon delimiters, and then extract numbers from each cell.
      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.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  16. Hello,

    I have this table

    ID(size-no.of purchase)

    2-1

    2-1

    2-4

    1-1,2-1,13-1

    2-1

    2-1

    10-1

    1-1

    2-1

    2-1

    3-1

    3-1

    2-1

    3-1

    3-2

    5-1,10-1,1-1,6-1

    3-1

    3-1

    3-1

    3-1

    2-1,1-1,3-1

    3-1

    3-1

    3-1

    2-1,1-1

    1-1

    3-1

    5-1

    5-1

    5-1

    5-1

    2-1

    2-1

    2-1

    1-1

    5-1

    2-1

    1-1

    2-1

    5-1

    5-1

    1-1,3-2,2-1,13-1

    2-1,1-1,3-1

    2-1,1-1,3-1

    3-1

    1-1,3-1,2-1

    5-1

    5-1,10-1

    2-1,1-1

    2-1,1-1

    5-2

    1-1

    3-1

    5-2

    1-2,2-2

    1-1

    3-1

    10-1,1-1

    1-1,3-1,2-1

    1-1,3-1,2-1,13-1

    1-1,2-1,3-1

    5-1,10-1,1-1,6-1,3-1,9-1

    5-1

    how to split and find sum of purchase?

    Thanks for Response!

  17. Please I want to separate N1,N2,N3 up to N48 in a cell to 48 cells

  18. Hey there.

    I have a text like this in one cell:
    "ABC: fdsdfsfdsdfds.ghfhghjhf.BCD: ghgfhfhhgfg.CDE: tretrerter.DEF: hgfhgfhggf."
    where those labels (ABC, BCD, CDE, DEF) can be a single or multiple words and they are always followed by ':'.
    What I'd like to get is
    1) to split those blocks in different columns
    2) to get the label for each of these new columns.

    So it would be something like:
    label1: "ABC"
    text1: "fdsdfsfdsdfds.ghfhghjhf." (it's ok also to get "ABC: fdsdfsfdsdfds.ghfhghjhf.")
    label2: "BCD"
    text2: "ghgfhfhhgfg." (it's ok also to get "BCD: ghgfhfhhgfg.")
    label3: "CDE"
    text3: "tretrerter." (it's fine ok to get "CDE: tretrerter.")
    label4: "DEF"
    text4: "hgfhgfhggf." (it's ok also to get "DEF: hgfhgfhggf.")

    I'm having problems detecting the delimiter for splitting, given that it can't be always (as you can see ABC should result = fdsdfsfdsdfds.ghfhghjhf.). So delimiter should be but only when it's followed by ':' prior to ...

    Any idea about how I can do it?
    Thanks.

    1. Hi!
      The separator, in your case, is the text between period and colon. But in your data, the period is both a separator and a regular character at the same time. I don't think I can help you.

  19. For the string 100005.5559Dress, (IN CELL A2)

    using the = LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},"")))),

    output is as followed:

    100005.555

    NOT 100005.5559.

    Please advise an amended code to have 100005.5559 reflected as the output.

    1. Hello!
      You need to add one more symbol to your formula: "."

      =LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9","."},""))))

      This should solve your task.

  20. Hi Folks,

    This helped me with some of my task, but there is one section I can't seem to extract. I have a string from a barcode scanner that looks like this:

    B55032324d12200420222537\r\n31483\r\n086-118090-900\r\nNWK12200420222536

    And I want to break it apart at the "\r\n" in separate cells, the order or orientation is not important.

    1. Hello!
      Writing a formula to separate 4 words is a very difficult task. I recommend split text with the Split Text feature.
      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.

  21. Thanks !!! you saved my life <3

  22. Super useful. I used it to separate out the domain in email addresses so I could see which emails were in the same domain like this....

    =LEFT(B2, SEARCH("@",B2,1)-1)
    =RIGHT(B2,LEN(B2)- SEARCH("@",B2,1))

  23. tabel Size w h A W H B&c W H D Or D&E

    Freezer : A - W: 48” x H: 28” | B & C - W: 26” x H: 30”|D - W: 48” x H: 26” 48 28 1 26 30 2 48 26 1
    Freezer : A - W: 64” x H: 29” | B & C - W: 27.5” x H: 21”D & E - W: 28” x H: 39” 64 29 1 27.5 21 2 28 39 2
    Freezer : A - W: 61” x H: 25” | B & C - W: 23” x H: 21”D & E - W: 25” x H: 29” 61 25 1 23 21 2 25 29 2

  24. Thank you!!!

  25. I have applied the following formula to my spreadsheet to split 1 cell that contains the information as follows:
    Cell = 3^4^12

    With the formula applied, the cell is split and separated into their own separate cells as follows:
    3
    4
    1

    The issue is that the formula I've applied does not taken into account that there may be numbers of more than 1 digit.
    So instead of the intended outcome being:
    3, 4, 12, the formula splits it as 3, 4 and 1

    Please advise if there is a way to solve this issue?

    formula to split 1st number:
    =LEFT(D3,SEARCH("^",D3)-1)

    formula to split 2nd number:
    =IFERROR(MID(D3,SEARCH("^",D3)+1,SEARCH("",D3,SEARCH("^",D3)+1)-SEARCH("^",D3))," ")

    formula to split 3rd number:
    =IFERROR(MID(D3,SEARCH("^",D3)+3,SEARCH("",D3,SEARCH("^",D3)+1)-SEARCH("^",D3))," ")

    1. INPUT DATA
      Column A
      (Blank)
      (Blank)
      ABCDEFGHI
      JKLMNO
      PQRS
      UVWX

      NEED OUTPUT
      COLUMN B
      ABCDEFGHI

      COLUMN c
      JKLMNO

      COLUMN D
      PQRS

      COLUMN e
      UVWX

  26. Hi. I have the below data, each containing information separated by the "^" sign.
    The length of this data is not consistent, with some containing 2 "^" and others with up to 15 "^"

    I've tried the LEFT, MID, RIGHT formula, but I do not know how to extend it to search up to 15 "^"
    Please can you help.

    Data:

    Colour (heading)
    Orange^Black^Red^Yellow^Grey

    Colour Reference (heading)
    O^BL^R^Y^GY

    Colour QTY to Order (heading)
    1000^1500^1300^500^2000

    I would like to split the data in each row, into it's own column/row
    So that in the end:
    Colour QTY to Order
    1000 - each in it's own row/column
    1500
    1300
    500
    2000

      1. Thank you for the quick reply.
        I was hoping there was a formula I could use, so that when the s/sheet is auto-populated with data, I can then add a formula that will automatically split the columns, as opposed to using Text to Columns.

        Please let me know.
        Thanks
        Joanne

        1. Hi Joanne,
          Assuming the titles of the columns are the values you are searching for you can get your table with a single formula.
          Titles like O, BL, R, Y, GY in B1:Z1
          Data into A2
          O^BL^R^Y^GY
          B^GR^1000
          B^GR^Y
          RD^5000
          1000^5000 etc

          Into B2 put the following :
          =IFERROR(IF(FIND("^"&B$1&"^","^"&$A2&"^")>0,B$1,""),"")
          drag formula to cover span of the table.

          I've added manually ^ to each end of the string to search to ensure all values are found (in case one end is missing a character)
          If it finds the ^xx^ in the string it puts it in the matching titles column.

  27. After using Python for years, it almost seems criminal that Excel doesn't include a "split" command within the UI. I created my own user-defined function (UDF) to accomplish this. This parser can pluck the Nth element from a given string provided there's a consistent delimiter, and it can work left to right or right to left. Code:

    Option Explicit

    Function GetLength(a As Variant) As Integer
    If IsEmpty(a) Then
    GetLength = 0
    Else
    GetLength = UBound(a) - LBound(a) + 1
    End If
    End Function

    Function FetchElement(RefCell As String, ReturnElementNbr As Integer, Delimiter As String, Reverse As Boolean) As String

    Dim Hierarchy As String
    Dim MyArray As Variant
    Dim ArraySize As Integer

    If Reverse = True Then
    If Right(RefCell, 1) = Delimiter Then
    Hierarchy = StrReverse(Left(RefCell, Len(RefCell) - 1)) 'remove trailing "\" and then reverse remainder of string
    Else: Hierarchy = StrReverse(RefCell)
    End If
    Else: Hierarchy = RefCell
    End If

    MyArray = Split(Hierarchy, Delimiter)

    ArraySize = GetLength(MyArray)

    If ReturnElementNbr > ArraySize Then
    FetchElement = ""
    ElseIf Reverse = True Then FetchElement = StrReverse(MyArray(ReturnElementNbr - 1))
    Else: FetchElement = MyArray(ReturnElementNbr - 1)
    End If

    End Function

    sample data to parse (let's say it's in cell "A1"): prvd00664966\PT00076084\PT00072170\PT00072157\PT00076116

    command: =FetchElement(A1,3, "\", TRUE)

    this will count the elements from right to left (TRUE flag used) with an assumption that the delimiter is a "\" and that we want the third element (parm = 3, above) in the list.

    Yes, UDFs are relatively slow. But this is a heck of a lot more readable and usable than having to use so many nested string functions in Excel

  28. Thank you for your instructions. Unfortunately I could not find my answer. If you could be so kind to help me. I have the following data:

    For example (123)
    For example (1)
    For (example) (344)
    For example (for) example (12)

    I need to remove the numbers and the brackets. I tried many ways but I have not found a solution. There is a formula for removing everything after and including brackets. However then I have a problem with data such as: For example (for) example (12) . In which it will not only delete the numbers but also the text.

    Is there a formula for deleting numbers in brackets? Or a way for me to remove it?

    Hope to hear from you soon.

    1. Hello!
      Here is the formula that should work perfectly for you:

      =SUBSTITUTE(SUBSTITUTE((CONCAT(IF(NOT(ISNUMBER( --MID(A1,ROW($1:$93),1))), MID(A1,ROW($1:$93),1),"")))," ",""),"()","",1)

      We have a tool that can solve your task in a couple of clicks — Ablebits Data - Remove Characters. 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.

  29. Hi There,

    How do I separate text by a pattern. However, the pattern can change and there is no common string that I can use to separate the cell into columns.

    For example,

    I have the text 2015 Mazda CX5 V6 White Bumper. I would like to split this by Make, Model, Year, Engine Type, Color, Part and others if any. The tricky part here is on the next row I could have 2003 Toyota Camry Bonnet Blue. I want all the makes to go into 1 column, all the models to go into 1 column, all the parts to go into 1 column and so on. The pattern can change as well. Because sometimes it can start with the make and end with model or it could be other as well. I want this process to be automated.

    Because the excel wouldn't understand what the make, model, year are, what we could do is we could probably add a drop-down on the heading of each column that has the list of makes, and another column would be models and so on. So, now when the system identifies make, it would have to search in all the drop-downs of all the columns and find where it is and put it on that column. I know it's a little complex, but I am sure it can be done.

    Any help would highly be appreciated. Thank you!!

  30. The goal is to split each Biblical Hebrew word into individual letters. However, Text-to-Column causes the nikkud/accents to separate from the base letter.

    I've discovered that putting a hyphen between each letter of the Hebrew word will cause Text-to-Column to separate the individuals characters intact.

    So, I went looking for a formula to insert dashes between letters and found one that works....but only for English letters/numbers. With the Hebrew, this formula separates out the nikkud/accents.

    https://superuser.com/questions/1371473/how-can-i-format-a-character-string-in-excel-to-insert-hyphens

    Sub InsertCharacter()

    Dim Rng As Range
    Dim InputRng As Range, OutRng As Range
    Dim xRow As Integer
    Dim xChar As String
    Dim index As Integer
    Dim arr As Variant
    Dim xValue As String
    Dim outValue As String
    Dim xNum As Integer

    xTitleId = "Put Dashes"
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)

    xRow = Application.InputBox("Number of characters :", xTitleId, Type:=1)
    xChar = Application.InputBox("Specify a character :", xTitleId, Type:=2)

    Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
    Set OutRng = OutRng.Range("A1")

    xNum = 1
    For Each Rng In InputRng
    xValue = Rng.Value
    outValue = ""
    For index = 1 To VBA.Len(xValue)
    If index Mod xRow = 0 And index VBA.Len(xValue) Then
    outValue = outValue + VBA.Mid(xValue, index, 1) + xChar
    Else
    outValue = outValue + VBA.Mid(xValue, index, 1)
    End If
    Next
    OutRng.Cells(xNum, 1).Value = outValue
    xNum = xNum + 1
    Next
    End Sub

    Question: Is there a way to tweak this formula so that it will recognize Hebrew 'complete characters'? Or is there another way to work with Hebrew in Excel?

    Thanks so much.

  31. problem string:
    /subscriptions/dummy-subscription-id/resourceGroups/my-resource-group-name/providers/Microsoft.Web/serverfarms/my-app-service-plan-name

    what i needed out of it?
    my-app-service-plan-name

    what expression got it?
    =RIGHT(I2,LEN(I2)-SEARCH("serverfarms",I2)-LEN("serverfarms"))

    thanks for guiding me towards

  32. Super thanks to you, you made my day really grate
    whith this formular :
    =LEFT(A10,LEN(A10)-LEN(D10))

  33. I need to split the value 12 into 9 2 1 in 3 different cellsand incase if the value becomes 13 i need it to ne splited inti 9 2 2. Is that possible? I need the value in the last colum keep changing if the value varies. Is there any possibile solution? Please help

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =D1-9-2

      Hope this is what you need.

  34. Looking for an automatic option to take whatever is typed into cell B3 to auto populate each character into rows beneith

    Ex: B3 =abcd 'is entered
    cells populate here:
    Cells B5 =a
    B6 = b
    B7 = c
    B8 = d

    cannot use text to column
    Any help???

  35. HI,

    I am looking for the number "02305255" to be separated from the hyperlink. Could you help get the Excel formula?

  36. Thank you for the helpful article and comments!
    I have been trying to separate a string into characters (so I can have each character into a separate column).
    Such as if I have a string “word”, I would like to to have it’s characters “w”, “o”, “r”, “d” in separate columns.
    Any help will be appreciated.

    1. Hello!
      If your text is written in cell A1, write the formula in B1:

      =MID($A$1,COLUMN()-1,1)

      Copy this formula column by column to C1, D1, E1, etc.
      I hope it’ll be helpful.

  37. Thanks!

  38. Thank you!

  39. I am using AbleBits Split by Characters function to split by commas into rows. No matter how many times I try it, it splits into columns instead. Can you help me understand what I'm doing wrong?

    1. Hello!
      Unfortunately, without seeing your data it is impossible to give you advice.

      I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

      We'll look into your task and try to help.

  40. I cannot get a cell separated and have it still identified as a whole number (to later average). I’ve tried substitute, replace, left functions... with no luck.

    It is separating out the _F from a number of temperature readings: 37 F, 42 F, etc

    Lil help?

    1. Hello!
      To remove characters and convert to a number, use the formula

      =--SUBSTITUTE(E1," F","")

      I hope it’ll be helpful.

  41. Hi,there!
    I want to separate # following with any number for example #8, #6, then I want to use number 8 or 6 as my value to use if function to print out different result, if 8 , print " 38 mm", if 6 print " 50mm" just a example. Thanks a lot!

    1. Hello!
      I’m sorry but I do not fully understand your goal. If you want to replace the entered value 8 with the text in the same cell, then this is possible only using a VBA macro. You can write either a value or a formula in a cell. If you need to convert # 8 to a number, you can use the formula

      =--SUBSTITUTE(A2,"#","",1)

      or

      =IF(SUBSTITUTE(A2,"#","",1)="8","38mm",IF(SUBSTITUTE(A2,"#","",1)="6","50mm",""))

    2. "I want to separate # following with any number for example #8, #6, then I want to use number 8 or 6 as my value to use if function to print out different result, if 8 , print " 38 mm", if 6 print " 50mm" just a example. Thanks a lot!" It is in the Same Cell, print also at the same Cell.

  42. I have a cell like "1 S Laser Beam, 3 M Pulse Laser, 18 Sansha Debris, 9 M Booster"

    what this means is that the cell has 1 qty of Small Laser Beam, 3 qty of Medium Pulse Lasers and etc.

    How can I parse this so that I can parse these quantities so that I can use them individually? Thanks

    1. Hello!
      You can split your text into cells in any of the ways described above. Use a comma as a separator. You can then extract a number from each cell using a formula. For example:

      =--LEFT(A1,FIND(" ",A1,1)-1)

      I hope it’ll be helpful.

  43. Hi there,
    How to split this? Can you tell me?
    151 Exc AvenueNey York, NY 10001
    123 Word AptMiami, FL 23456
    I Want city name to be in separate field and presiding address in separate field. Any formula which puts the word starting in CAPITAL after SMALL letter to a different field?

  44. I have a challenge on number search in a cell that is formatted as text. I have a TAB "Systems Software" with column G that have cells with multiple numbers (not necessarily in numeric order) separated by commas (i.e., G4 contains 4,28,9,14,44,23,10,104) in each cell. Each cell may have a single number, or be empty or a string of numbers separate by a commas. I need to find if a given specific number (i.e., 4) is in that string of numbers that are contained in a cell formatted as text. It is number four that I am searching for in that sample, not the four in fourteen or forty four or one hundred four. If I find the search number within the number in the string, I can print an X in the cell that is doing the search. Otherwise, if the number is not found, leave the cell empty. Any help will be greatly appreciated.

    1. Looking for just 4 and not the 4 in 14,24,104 etc?
      Simply search for ",4," Include the commas.
      The only time you need any special check is the first or last numbers in the string.

      Saiph

  45. Hi Team,
    He is a smart guy.
    smart people are everywhere.
    I want to print the word which is right after the word "smart", can some one please help me
    Thanks,
    Anand

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =MID(B1,SEARCH("smart",B1,1)+6,LEN(B1)-(SEARCH("smart",B1,1)+5))

      I hope this will help

  46. For those wondering how to split a chain of characters into more than 3 cells :
    I'm too lazy to translate my formulas. The functions are in french so I'll just translate them here :
    GAUCHE = LEFT
    DROITE= RIGHT
    STXT= MID
    CHERCHE= SEARCH
    NBCAR= LEN

    So, I wrote in A2 (yeah I started from A2 instead of A1) :
    test1;test2;test3;test4;test5;test6
    I want to separate this into 6 cells.
    In B2, I type :
    =GAUCHE(A2;(CHERCHE(";";A2;1)-1))
    This gives me "test1".
    In C2, I type :
    =STXT(A2;NBCAR(B2)+2;CHERCHE(";";A2;(NBCAR(B2)+2)-CHERCHE(";";A2))-1)
    This gives me "test2"
    In D2, I'll write :
    =DROITE(A2;NBCAR(A2)-NBCAR(B2)-NBCAR(C2)-2)
    This gives me "test3;test4;test5;test6"
    From there on, I can reiterate the two other formulas to obtain in E2 : "test3", in F2 : "test4", in G2 : "test5;test6" and so on and so on.

  47. Hi Team - Thanks for this material.. absolutely useful. However I am a bit stuck (think i am being naive as missing something :) ).. From example above, how am i able to split all the string between 2 characters as there are multiple occurrences.
    Above example shows clearly on how to extract between 1st and 2nd occurrence. How about 2nd and 3rd occurrence, 3rd and 4th occurrence etc.. I want this to be in a single formula.
    Example:
    Dress-Blue-S-US-20-Yes
    Expected output:
    Blue (Answer available on top of this post)
    S
    US
    20
    Yes

    1. =TextSplit(TextAfter([SourceCell], "-"), , "-") will parse everything after the first "-" into separate cells in a row below the cell containing this formula, just as you have in your example.

    2. Hello!
      Dividing a long text into separate words using a formula is a very difficult task. If the text contains more than 4 words, then solving the problem using the formula does not make sense. Use the standard Text to Columns tool or the Ablebits Split Text tool, which is described earlier in this article.

      1. Hi - Unfortunately i cannot use the options in Excel as I can only use it via formula due to the fact that the incoming value string is a variable that will be written at runtime & has to be decoded into above requirement during playback/runtime
        Thanks

  48. I am working with power query and my results have jumbled together movie names with release dates, where the release date (format August 7, 2020) is combined with the movie name. the movie names of course are different lengths, and the dates are different lengths depending on the release date. I'm not finding any way to split my data between the movie name and the release date (example: The Broken Hearts GalleryAugust 7, 2020). Can you show me how to do this?

    1. I’ve found one way that adds a small macro (that you don’t need to keep)

      ALT + F11
      Create a module and add the following code:
      Function IS_DATE(rng) As Boolean
      IS_DATE = IsDate(rng)
      End Function

      This will tell you if a date is valid or not (true/false)
      Then we test your movie titles by trying to find the date on the end
      There will always be 4 digits for year, 1 for comma, 1 space, at least one digit for day and then the month. A span of 7 possible lengths. May x – September xx
      Assuming your title/date is in A1.
      In B1 put:
      =IF(is_date(RIGHT(A1,11))=TRUE,11,IF(is_date(RIGHT(A1,12))=TRUE,12,IF(is_date(RIGHT(A1,13))=TRUE,13,IF(is_date(RIGHT(A1,14))=TRUE,14,IF(is_date(RIGHT(A1,15))=TRUE,15,IF(is_date(RIGHT(A1,16))=TRUE,16,IF(is_date(RIGHT(A1,17))=TRUE,17,IF(is_date(RIGHT(A1,18))=TRUE,18,""))))))))

      The formula trys cropping the string at 11 characters long to 18 and tests each one to see if it’s a real date or not. The month is the decider so short spelled or additional characters make this a false date. Only the right length will actually create a valid date and returns the trim length
      The result is the number of characters to cut the string at

      Movie title goes in C1:
      =left(A1,len(A1)-B1)

      Date goes into D1
      =right(A1,B1)

    2. Hello Michael!
      To separate the date from the text, you can use the formula

      =RIGHT(F1,LEN(F1)-FIND("~",SUBSTITUTE(F1," ","~",LEN(F1)-LEN(SUBSTITUTE(F1," ",""))-2)))

      I hope my advice will help you solve your task.

  49. Hi..
    Plz help me to count the frequency in the following
    1234567 = frequency is 7 than plz help me to find correct formula for count frequency in the following examples
    1.3..67 = 4 and
    7 = 1 and
    ...3...7 = 2

    Plz help

    1. Hello!
      Tell me specifically what exactly you want to calculate. "1234567 = frequency is 7" is not a frequency. Perhaps you want to find the number of digits? Or the number of characters? Or something else?

  50. Hello,
    I'am looking for this function:
    I have a cell like this : XTORM SOLID BLUE MICRO USB CABLE 1MTR
    I want to split this cell (all cells with text) after the 3rd or 4rd space, like this
    XTORM SOLID BLUE
    MICRO USB CABLE
    1MTR
    Those words undernead in 1 cell
    Is this possible?? i hope thanks!!! greetz remco

    1. Hello Remco!
      To divide the text into 3 cells after every third space, use the formulas

      =LEFT(C1,FIND("*",SUBSTITUTE(C1," ","*",3))-1)

      =MID(C1,FIND("*",SUBSTITUTE(C1," ","*",3))+1, FIND("*",SUBSTITUTE(C1," ","*",6)) -FIND("*",SUBSTITUTE(C1," ","*",3)))

      =IFERROR(MID(C1,FIND("*",SUBSTITUTE(C1," ","*",6))+1, FIND("*",SUBSTITUTE(C1," ","*",9))-FIND("*",SUBSTITUTE(C1," ","*",6))), MID(C1,FIND("*",SUBSTITUTE(C1," ","*",6))+1, LEN(C1)-FIND("*",SUBSTITUTE(C1," ","*",6))))

      I hope this will help

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