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

  1. 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.

  2. 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

  3. 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!!

  4. 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

  5. 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.

  6. 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.

  7. 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!

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

  9. 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.

  10. 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.

  11. 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.

  12. 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

  13. 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.

  14. 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.

  15. 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.

  16. 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???

  17. HI,

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

  18. 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.

  19. 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.

  20. 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. "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.

  21. 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.

  22. 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

  23. 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

  24. 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

  25. 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.

  26. 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?

  27. 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

  28. I have output from a Qualtrics survey for "select all that apply" questions. I need to import it into a statistical package for analysis (SPSS). The data now have numeric codes for answer choices, but when imported into the stats package are read as string (alpha) and separated by commas. I am trying to use Exsel to separate these values into columns, but need a different column for each value. Some questions have up to 5 values, but users may have only checked 1, 2 or even 4 or 5 options, and so their values vary.

    Original Data: --> New array, where | | indicates a separate column
    1, 2, 5 becomes | 1 | 2 | | | 5| (i.e. the columns for the values 3 and 4 are left blank)
    2, 3, becomes | | 2 | 3 | | | (i.e. the columns for the values 1, 4 and 5 are left blank)
    4 becomes | | | | 4 | | (i.e. the columns for the values, 1, 2, 3 and 5 are left blank)

    Text to column separates them, but then I have a column with a mixture of answers (e.g. 1, 2, 4 based on example above) yet I need to create a dichotomous variable for answer choice 1, a separate one for 2, etc. Any ideas? Thank you.

    1. Hello Sara!
      If I understand your task correctly, please try the following formula:
      For example, the value of cell A1 is 1,2,5
      Formula in cell B1
      =IFERROR(IF(--FIND("1",A1,1) > 0,1,""),"")
      Formula in cell C1
      =IFERROR(IF(--FIND("2",A1,1) > 0,2,""),"")
      Formula in cell D1
      =IFERROR(IF(--FIND("3",A1,1) > 0,3,""),"")
      Formula in cell E1
      =IFERROR(IF(--FIND("4",A1,1) > 0,4,""),"")
      Formula in cell F1
      =IFERROR(IF(--FIND("5",A1,1) > 0,5,""),"")
      I hope this will help, otherwise please do not hesitate to contact me anytime.

    2. I don't know if you can do it other than with a formula:

      ______A___ | ____________B______________ ... ____________F______________
      1 | "1,2,5" | =if(iserr(find("1",A1)),"",1) ... =if(iserr(find("5",A1)),"",5)
      2 | ...
      3 | "4" ____| =if(iserr(find("1",A3)),"",1) ... =if(iserr(find("5",A3)),"",5)

  29. Hi

    I would like to take a string of numbers in one cell and separate it by "-". The original data came from a string of columns, which I concat into one cell.
    Orignal data:
    705000 336 10 01 000 0000 35500
    Used Concatenate for the result below
    7050003361001000000035500
    want it to be
    705000-336-10-01-000-0000-335500

    1. Instead of concatenating the number and then splitting it back out ...
      Use a1 & "-" & b1 & "-" & "c1 to concatenate with the -'s already in place.

    2. Hello Shamrock!
      Please try the following formula:
      =SUBSTITUTE(A1," ","-")
      or
      =SUBSTITUTE("705000 336 10 01 000 0000 35500"," ","-")
      Hope you’ll find this information helpful.

  30. Hello Sir,

    I have data '1234 to 56478' and wanted add comma only for values using excel function and data should look like '1,234 to 55,678'
    How do I do this?

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

      =LEFT(A1,FIND(" ",A1,1)-4) & ","&MID(A1,FIND(" ",A1,1)-3, LEN(A1) - FIND(" ",A1,1)+1) & ","&RIGHT(A1,3)
      I hope it’ll be helpful.

      1. Thank you Alexander, It is working for the data which I had given as example...But comma needs to added for every thousands for example for data like '1020000 to 4080000' should be coming as '1,020,000 to 4,080,000' which is not coming now....and also I have the data like'0 to 1360000' and '1360000 +' where this formula is not working.
        Can you help me out with this?

        1. I have bunch of excel data which looks like:
          1234 to 56478 (Given formula is working here)
          0 to 2450000
          1090000 to 4080000
          1360000 +
          1234 to 56478

          1. Hello Santhosh!
            Please use the following formula
            =TEXT(LEFT(A1,FIND(" ",A1,1)-1),"#,##0") & MID(A1,FIND(" ",A1,1), FIND(" ",A1,FIND(" ",A1,1)+1) - FIND(" ",A1,1)+1)&TEXT(RIGHT(A1,LEN(A1) - FIND(" ",A1,FIND(" ",A1,1)+1)),"#,##0")
            If there is anything else I can help you with, please let me know.

  31. How can I remove http:// from a website string?

  32. I need a formula for to split:
    1/03/2019 12:31:27 PM
    to make it read
    01/03/2019 in one column and 12:31 PM in another column
    Thank you!

    1. If your data is in A1, in B1 put =INT(A1). Format it as date short. In C1 put =A1-B1 and format it as Time

      Saiph

  33. Hi,
    I have a text file containing name, emails, address and many more its all 42 columns and doesn't have any headers and delimiters. I want the data in order as first name and last name in column and address in one column. I need a solution how to grab one records address data in one column as you see sample data that address is separated with spaces can you get me a solution . Here is the sample of my data.

    4391 Grig Mcdaniil mail@hotmailcom 3619 N. Tiffany Drivi Beavercreek NY 75637 5046851665 US MALE 09/30/1959 178 182 AB+ Grig Mcdaniil Frank Blacker ATHENS IL 61516 US N.A NO YES NO NO N.A FR-DLI09-i3089 09/30/1959 $200.00 Grig Mcdaniil Pamela Toloza BaX_OlaCm-2441 09/30/1959 MALE Other VALIUM37.5 MG 90 $2.85 $171.00$20.00 $191.00

    4392 Harold Davis mail@infinet 488 Wrins Nist Ct bay city FL 54311 5260805557 US MALE 10/17/1952 173 182 AB+ Harold Davis Owen K Ritter DENVERCA N.A US 9002005495 YES NO YES NO NO Yt_i7-Un-10375 10/17/1952 $250.00 Harold Davis Heidi Schultz BaX_OlacM-52110/17/1952 MALE Gas PHENTERMINE 37.5 MG 90 $1.90 $505.00$20.00 $525.00

    1. Apologies. Failed to Reply, but posted the answer as a new question.(77)

  34. I'm trying to get the text after the third "/" in a list of class names in the form:
    MIL/MZ/AD/INT-B1-BLUE-29-10-19-Tue-W-F-10:00-13:00
    With the formula below from the top of your thread I can get rid of MIL/MZ/ but I still need to get rid of AD/
    =RIGHT(F2,LEN(F2) - SEARCH("/", F2, SEARCH("/", F2) + 1))
    is giving me
    AD/INT-B1-RED-23-09-19-M-W-Th-18:30-21:30
    I tried playing and came up with:
    =RIGHT(F2,LEN(F2) - SEARCH("/", F2, SEARCH("/", F2, SEARCH("/", F2) + 1)))
    Which gives the same result as:
    =RIGHT(F2,LEN(F2) - SEARCH("/", F2, SEARCH("/", F2) + 1))
    I really don't understand the syntax so I've reached an impasse. If you are able to help I'd be grateful.
    Thanks in anticipation.

    1. You have the answer, just need to do another RIGHT command to get the characters to the right of the remaining '/' symbol.
      =RIGHT(RIGHT(B2,LEN(B2)-(SEARCH("/",B2,SEARCH("/",B2)+1))),LEN(RIGHT(B2,LEN(B2)-(SEARCH("/",B2,SEARCH("/",B2)+1))))-SEARCH("/",RIGHT(B2,LEN(B2)-(SEARCH("/",B2,SEARCH("/",B2)+1)))))

  35. Hi guys, I have a situation here as i need to split this one sentence into different columns IN EXCEL using a formula, as all words having spaces of different types.

    RDH10265/2 12,00 PC OPTICAL TRANSCEIVER/SFP+ SM 10.3Gb/s 1.4
    Thanks

    1. Use Find & Replace and replace 2 spaces with 1 space over the whole column. Run it multiple times till no changes occur. Now you can run formula and split out columns on single spaces.

  36. how will u split the below sting where there is only enter key
    1 Unit Fodder Grass Bijankur-BB2 50 gm Rs. 335
    1 Unit Fodder Grass Alamdar 51 1 kg Rs. 675
    1 Unit Fodder Grass Bijankur-BB2 50 gm Rs. 335
    1 Unit Fodder Grass Alamdar 51 1 kg Rs. 675

    1. Depending on where you want to break the text you can use LEFT, RIGHT and MID string.
      =LEFT(A1,FIND("Rs.",A1)-1) to trim off the price
      =NUMBERVALUE(RIGHT(A1,LEN(A1)-FIND("Rs.",A1)-3)) to get the price

  37. I have data in a column of cells similar to the following:
    A, B, C
    A
    B, C
    A, C
    B
    So now I want to filter the column by the data. However when I do this< my choices would be:
    A, B, C
    A
    A, C
    B
    B, C
    Where I really just want to filter by:
    A
    B
    C
    Should I separate the data by a different character to get this? Or is this something I need to dig further into to do in VB? Currently the data is entered into the cell using a drop down box, & then have some VB coded to have multi, non-repetitive selections that are separated by a comma. Thanks.

    1. Another character or assigning a value seems the easiest. Like Binary. A=1 B=2 C=4.
      Then a custom filter = selecting A will show all 1,3,5 and 7
      Selecting B will show 2,3 and 6
      Selecting C will show 4,5 and 6

  38. How about
    71200 Online Order Fees $376.12

    to 3 columns.

    Account Description Amount (with no dollar$)

    1. in A1 : 71200 Online Order Fees $376.12
      in B1 : =LEFT(A1,FIND(" ",A1)-1)
      in C1 : =MID(A1,FIND(" ",A1)+1,FIND("$",A1)-FIND(" ",A1)-2)
      in D1 : =NUMBERVALUE(RIGHT(A1,LEN(A1)-FIND("$",A1)))
      This is assuming no one puts a $ in the text...

  39. Hi,
    I have a question need for it answer by formulas to split according to dash all the contents of cell into separator in many cells beside each other as following example:
    What I have in Cell A1:
    Orange com - Emad Farag - Telephone bill - Month April 2019 - paid from cash
    What I need in Cells C1, D1, E1, F1, G1
    C1 D1 E1 F1 G1
    Orange com Emad Farag Telephone bill Month April 2019 paid from cash

    Thanks

    1. Hi Emid, Is your problem solved or I can help you in solving the problem.

  40. Hi there,
    I want to split a text in one cell into different columns but can the text be rearranged in a specific order?
    Cell A1 = Yellow/Magenta
    Cell A2 - Magenta/Yellow
    I would like Cell B1 = Yellow, C1 = Magenta, Cell B2 = Yellow and Cell C2 = Magenta

    Is there a formula that can do this?

    Thank you

    1. Use a column specific formula
      Assuming Data is in A1,A2 etc
      In B1
      =IFERROR(IF(FIND("Yellow",$A1)>0,"Yellow",""),"")
      In C1
      =IFERROR(IF(FIND("Magenta",$A1)>0,"Magenta",""),"")
      In D1
      =IFERROR(IF(FIND("next color",$A1)>0,"next color",""),"")
      The iferror stops the formula failing and puts a blank if the color is not found. If it is found anywhere in the string then it will show.
      The only problem you might have is putting in a "Light Yellow" as this will also flag as Yellow

  41. I have a single column of text which I want to parse into various columns, but the delimiter is currently multiples of blank spaces in front of the text. Such as the data which should be in the first column has 5 blanks in front, the data which should be in the second has 10 blanks in front and the data which should be in the third column has 15 blank spaces in front of the text. Example of how this looks in the current single column:

    Column1 text
    Column2 text
    Column3 text.

    The amount of text may vary in each column, but the leading blank spaces are constant. Thanks for your help!

    1. That didn't show up correctly, let me use _to emulate a blank space (but there are actually only spaces, no leading characters):
      _____text column 1 info
      __________text column 2 info
      _______________text column 3 info
      I hope that is more clear. Thanks.

      1. data in A1.
        These formulae take the data before the first 10 spaces as column 1, between 10 and 15 as column 2 and to the right of 15 spaces as column 3
        B1 =TRIM(LEFT(A1,FIND(" ",A1)))
        C1 =TRIM(MID(A1,FIND(" ",A1),FIND(" ",A1)-FIND(" ",A1)))
        D1 =TRIM(RIGHT(A1,LEN(A1)-FIND(" ",A1)))

        1. B1 =TRIM(LEFT(A1,FIND("10",A1)))
          C1 =TRIM(MID(A1,FIND("10",A1),FIND("15",A1)-FIND("10",A1)))
          D1 =TRIM(RIGHT(A1,LEN(A1)-FIND("15",A1)))

          website took out my spaces.
          numbers in the speechmarks are number of spaces.

  42. =LEFT(TRIM(B34),FIND("~",SUBSTITUTE(B34," ","~",LEN(TRIM(B34))-LEN(SUBSTITUTE(TRIM(B34)," ",""))))-4)

    Kindly define this formula.

    1. Find the last space in the B34 string and take everything to the left of that, less 4 characters. The string must have spaces for this to work.

  43. SEARCH("-",A2,SEARCH("-",A2)+1) - SEARCH("-",A2) - 1)

    1. Hi Nathan,

      To explain it better, let's have a look at the full formula:
      =MID(A2, SEARCH("-", A2) + 1, SEARCH("-", A2, SEARCH("-",A2) +1) - SEARCH("-", A2) - 1)

      1. Start_num argument: SEARCH("-",A2) + 1. It finds the position of the first character to extract; +1 is needed to start extraction beginning with the character that follows the first hyphen, not including the hyphen itself.

      2. Num_chars argument (how many characters to extract): SEARCH("-", A2, SEARCH("-",A2) +1) - SEARCH("-", A2) - 1. To find the position of the 2nd hyphen, you put SEARCH("-",A2)+1) in the start_num of the first SEARCH function to tell it to start searching beginning with the character after the 1st hyphen (this way, you exclude the first hyphen from search). Then, you subtract the position of the 1st hyphen from the position of the 2nd hyphen to find how many characters are between them. But this number includes the 2nd hyphen, so you put -1 at the end to remove the 2nd hyphen from the returned substring.

  44. I have a full description of a part in one column and need to pull over no more then 35 characters. I need to do this at a "space" but not go over 35 characters on my limit. please help!

    thanks!!

    1. =LEFT(A1,FIND("☃",SUBSTITUTE(A1," ","☃",LEN(LEFT(A1,36))-LEN(SUBSTITUTE(LEFT(A1,36)," ","")))))
      (Left(a1,36)) This trims the string to a maximum of 36 characters. 36 because if the 36th is a space then 35 chars will be returned and if not, then the break will come earlier in the string and less will be returned.
      The statement LEN-LEN(substitute) finds the length of the string, and then the length with the spaces removed. This gives the number of spaces in the string.
      The Substitute inside the Find statement then changes the last space to a snowman, and the Find returns that position in the string.
      Finally the left statement cuts the string at that point and gives your value.

  45. I have the following data in one cell. i like to spit these data in different cells. is anybody can help?
    1/15/2019 4320 610285062 1 30 0930 1143 88 167.75 14.26 $182.01

    1. in Excel select the cell/column.
      On the ribbon, choose Data tab, "Text to Columns" and select delimiter and then untick Tab and select Space

  46. I have a text string that I am trying to split which has different characters to split in the string.
    Example string
    Product Support 2018 H2~TC03986-01-ICS

    I want to split it as "Product Support 2018 H2" "TC03986-01" and "ICS"
    I am not sure if it is possible to use two different separators, but the "~" causes a problem. I have tried to use CHAR(126) but this does not work. I have also tried to replace the ~ using search and replace.

    1. Part answer:
      =LEFT(C20,FIND("~",C20)-1) to get product

  47. I want to extract the City from the following rows. The City is the word in between the LAST space of the field and the comma of the field. The comma is always the last character in the field. What formula should I use?
    1111 FM 1585 LUBBOCK,
    2222 10TH ST SHALLOWATER,
    333 COLTON AVE LUBBOCK,

    1. Hello, Claudia,

      Suppose your data are in column A, please try to enter the following formula in cell B1 and then copy it down along the column:

      =MID(A1, FIND("#", SUBSTITUTE(A1, " ", "#", LEN(A1)-LEN(SUBSTITUTE(A1, " ", ""))))+1, LEN(A1)-FIND("#", SUBSTITUTE(A1, " ", "#", LEN(A1)-LEN(SUBSTITUTE(A1, " ", ""))))-1)

      Hope this is what you need.

  48. Hi,

    how can you split string "abcdef" in a single character per row?

    1. Swati:
      The simplest method to accomplish what you want is to use the Text-to-Columns tool. People seem to turn their collective nose up at using this tool and I don't know why.
      Highlight the cell containing the data to split.
      Select under the Data tab on the ribbon select the Text-to-Columns option.
      In the Text-to-Columns window select the Fixed Width radio button.
      Left click the places where you want the data to be split.
      Click finish.
      This will leave the first character in the original cell and the data will be in separate cells.
      If you have multiple rows containing data to be split, highlight all the rows and go through the same process.
      You can split multiple rows, but not multiple columns.

      1. Doug, the reason that this doesn't work for me is that I have an export where the data looks like this: 422353-1-5 but the first number can be any length up to 7 digits. If the first number is from about 1900 to 9999, the Excel sheet has those cells formatted as a date with the correct ones formatted like a string. I need to determine on a row by row basis whether it's a date so I use an IFERROR function to split it into 3 columns.

        If I only had to do this once, I would simply do this: =IFERROR( YEAR(B2)&"-"&MONTH(B2)&"-"&DAY(B2), B2) and then split it into 3 columns, but this is something that needs to be done all of the time and it's faster to do it by having a proper formula that can be copied down.

  49. I need a help with a formula.. We get some data interfaced to our system but due to some error at source it is stuck in the temporary area and does not move to the live area. The error is for eg.
    DXB EK LON AC YYZ 234.123567.34 the error is that 2 amounts are merged. The result I require is that after 2 decimal places I need a space like DXB EK LON AC YYZ 234.12 3567.34. Would I be able to get the correct string in another cell using excel formula.

    1. =LEFT(A1,FIND(".",A1)+2)&" "&RIGHT(A1,(LEN(A1)-FIND(".",A1))-2)

  50. Hello

    I have following value

    Cell A1 = 621.33Ari
    A2 = 620.4.7Dsi(5)
    A3 = 53:62Asi:21

    I want separate text from number

    For example

    Output to cell B1 = 621.33 Cell C1 = Ari
    B2 = 620.4 Cell C2 = Dsi(5)
    B3 = 53:62 Cell C3 = Asi:21

    Please help me

    Thanks

    1. Bhagwan:
      I think you'll need two little formulas to get this done.
      For the first set of data enter this in B1
      =LEFT(A1,6) and this in C1 =RIGHT(A1,3)
      For the next two sets use this in B2 =LEFT(A2,6) and enter this in C2 =RIGHT(B2,6)
      Copy these two formulae down the column for B3 and C3.

  51. Hi, How do I get the text before the comma?
    Ex: Cell A1= My Name, Eddie

    Output to Cell B2 = My Name
    Outout to Cell B3 = Eddie

    I don't want to use the "text to column function" because I'll have to do it manually when a new entry comes in.

    Appreciate if you could help to demonstrate a string for this function.

    Many thanks in advance

    1. Eddie:
      In B2 enter this:
      =LEFT(A1,SEARCH(",",A1,1)-1) this will return "My Name"
      In B3 enter this:
      =RIGHT(A1,SEARCH(",",A1,1)-2) this will return "Eddie".

  52. Hi,
    i have values in one column as below.
    ABC (1), IJK (20), LMN (15), XYZ (5)
    LMN (10), XYZ (25), ABC (15), IJK (3)
    XYZ (25), ABC (225)

    i want create new column and extract value for ABC as below
    1
    15
    225

    I tried using MID function as explained, but i get value as below.
    =MID(A2, SEARCH("ABC",A2) + 5, 3)

    1)
    15)
    225

    Can you pls help?

    1. Had to use the FIND command to get the last bracket and subtract positions.
      This should work for you.
      =MID(A1,FIND("ABC",A1)+5,(FIND(")",A1,FIND("ABC",A1)+5))-(FIND("ABC",A1)+5))

      Locates the ABC starting character and adds 5 to get to the number
      Then second part finds the first ) after the number
      Then it subtracts the position of the ) to get the number for the mid statement
      Use VALUE to convert this output value to a number if you want to perform sums on it

  53. How could I split

    L18000160290MASS LLC

    into
    L18000160290 MASS LLC

    Basically I want to take the first 12 characters into a separate cell

    1. Keven:
      I think this should work.
      Where L18000160290MASSLLC is in A1
      =LEFT(A1,12)

  54. Hi,
    I have figures in a line beak
    59/220118
    64/080318
    64/100418
    128/100518
    192/110617
    In another column I need to add up the numbers before "/". Do you have a formula for that please?
    Thanks in advance.

    1. Try using this. If your value is in H3...
      =VALUE(LEFT(H3,FIND("/",H3)-1))

    2. Naveen:
      I think the easiest thing to do in a situation like yours is to use the Text-to-Columns tool that is built in to Excel.
      Click the Data tab on your ribbon, then choose Text-to-Columns, then click the Delimited button, then choose the Other option and enter a / in that field. You should then see how your data will be separated into two columns. Click Next and the data will be split into two columns beginning with the column the data was originally in. If you want the split data to be put into a different cell then enter that address in the Destination field in the third step. You can also choose not to import the data after the / . In this last step select the column heading that holds the data you don't need and choose the Do Not Import Column.
      Click finish and you have your data in its own column ready to sum.

  55. Hi
    How to split number of live sheet into individual no and add them
    Ex: 254.56 into 25456
    Add:2+5+4+5+6
    I split no but unable add them

    1. Umesh:
      In an empty cell enter =Sum(Range Holding Values)
      For example if the numbers are in cells B2,C2,D2,E2 and F2 then =SUM(B2:F2)
      Be sure to format the cells B2:F2 as number.

  56. Hi can you please help me split 4017.524120.9822..0000.0000.3137 as
    4017
    524120
    9822
    0000
    0000
    3137

    1. Soni:
      You can select the cell with the data and then use the Text-to-Columns tool under Data. Use "." as the delimiter and once the data is in separate cells you can copy the cells and paste into a blank cell using the Paste Special/Transpose option.
      If you do a lot of this work The AbleBits Ultimate Suite is probably a better option. It has many tool to help work with data. Splitting your type of data into rows is one of the tools.

  57. In order to split string by dash I wouldn't bother to construct different formulas for each part.
    I would, rather, use one single (similar) formula for all columns
    For example:
    Type the following formula into cell B2 and copy-drag it to the right.
    =TRIM(MID(SUBSTITUTE("-"&$A2&" ","-",REPT(" ",255)),(COLUMN()-1)*255,255))
    ----------------------------
    Michael (Micky) Avidan
    “Microsoft® Answers" - Wiki author & Forums Moderator
    “Microsoft®” Excel MVP – Excel (2009-2018)
    ISRAEL

    1. This did the trick for the task I was working on. May I ask what the elements of the formula mean?

    2. Thank You!!

  58. Hi Svetlana,

    I have a worksheet containing a single column as follows:

    1 F01K2502F01L1504A23K1165

    2 C09J12518B23K524C07C30988C07C31724C07C31734

    I would like to split this single column into multiple columns, like:

    1 F01K2502 | F01L1504 | A23K1165 (3 columns)

    2 C09J12518 | B23K524C07 | C30988C07 | C31724C07 | C31734 (5 columns)

    Any advice?

    Thanks a lot beforehand.

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  59. Hi Svetlana,

    Thanks for your help on this. I hope you are still supporting this post.

    The left, mid & right functions only work for 3 columns of data within the source cell. My data has 6 columns with 5 delimiters (",").

    If I use LEFT it returns the "first column's data",
    If I use MID, it returns the "second column's data",
    If I use RIGHT, it returns everything to the right in column "3", which is actually Column 3, 4, 5 & 6.

    Is there any way to "count" the delimiters, then return the data before/after the specified Xth delimiter? In other words, similar to VLOOKUP where the formula uses "col_index_num" to specify which data is wanted.

    Unfortunately, the data I am trying to extract to place in 2 different cells is in the second & fourth columns.

    I have isolated below between *** ***

    12/15/2017 16:10,***DYSINGER EAST***,23326,***2163.11***,3150,-9999

    This is realtime data from .csv url through Data --> Refresh All, so using the Text to Columns feature or add-ins is not possible. Also I am using MS Excel for Mac, and so far cannot get Excel to recognize the "," in the text string as the delimiters to separate the string automatically into proper columns.

    Thanks for any help on this!

    1. Hello,
      Please try using a VBA macro. We are always ready to help you, but we do not cover the programming area (VBA-related questions).
      You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.
      Sorry we can’t assist you better.

  60. Pls see if you can split the following, in Excel-2010 ...

    1 apple. 2 orange. 3 pear. 4 banana
    [this is all in a single cell]

    into ...

    column A column B
    1 apple
    2 orange
    3 pear
    4 banana

    Thank you.

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  61. how to split numbers as mentioned below?
    999999999.99 to 99 99 99 999.99;
    99999999.99 to 9 99 99 999.99;
    9999999.99 to 99 99 999.99;
    999999.99 to 9 99 999.99.
    and so on.

    Thanks in Advance.

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =SUBSTITUTE(TEXT(A1,"#,##0.00"),","," ")

      Hope this will work for you

  62. 08121804902
    hi there,
    081317003321
    081315004023
    08121002069
    08121803616
    08121001992
    081316000612
    081316002389
    08129000777
    08121002877
    can i ask help how to split between 12 digits & 11 digits different rows?

    1. Use LEN to return the length and test on that.
      =If(len(a1)>11,a1,"")

  63. Hello Svetlana,

    I need your help on this.
    Is there a way that I can split a string of text in a cell from

    "This is 2nd enquiry from this client. The 1st time wasSep-329 in Union TalkHusband's name: ChanWaiCheongTel:9*****"

    into

    "This is 2nd enquiry from this client. The 1st time wasSep-329 in Union Talk Husband's name: Chan Wai Cheong Tel:9*****"

    I have 44500 records having this problem. I tried doing a VBA code, to add space whenever there is a CAPs letter but it wont work for some records as the outcome will be: "paid via VISA" into "paid via V I S A"

    Please do help. Thank you.

    1. I have managed to solve this with a VBA Code.

      Function SplitCaps(strIn As String) As String
      Dim objRegex As Object
      Set objRegex = CreateObject("vbscript.regexp")
      With objRegex
      .Global = True
      .Pattern = "([a-z])([A-Z])"
      SplitCaps = .Replace(strIn, "$1 $2")
      End With
      End Function

      If any of you have another alternative please do share.

      Thanks

  64. Hi

    I want to split the below into columns
    90FB5B8F6B40
    001BBA9AB00
    001BBA99FE00

    I want my end result to be
    90:FB:5B:8F:6B:40
    00:1B:BA:A9:AB:00
    00:1B:BA:99:FE:00

    I thought if i can split the text to single columns and concatenate to join them back inserting the ":" that would help. So i needed to know how to separate the text string into columns. Is that possible?

    1. Based on your data being in A1, and always being the same length and expected return

      =LEFT(A1,2)&":"&MID(A1,3,2)&":"&MID(A1,5,2)&":"&MID(A1,7,2)&":"&MID(A1,9,2)&":"&MID(A1,11,2)

      if want them to be in separate column start with LEFT(A1,2)
      and then for every subsequent col copy the MID(A1,3,2) parts

  65. Hello Svetlana Cheusheva,

    Can you help me to extract "Fort Walton Beach", "Jacksonville Beach","Wethersfield",etc.
    These are examples:
    545 Ashley Court, Fort Walton Beach, FL 32547
    713 Arch Street, Jacksonville Beach, FL 32250
    341 Cardinal Drive, Wethersfield, CT 06109
    134 Main Street East, Anchorage, AK 99504
    211 Oak Lane, West Lafayette, IN 47906
    458 Williams Street, El Dorado, AR 71730
    913 Liberty Street, Saint Paul, MN 55104
    860 Somerset Drive, Acworth, GA 30101
    36 Briarwood Drive, Dekalb, IL 60115

    Thank you so much.

    Tien

    1. =MID(A10,FIND(",",A10)+2,(FIND(",",A10,FIND(",",A10)+1)-(FIND(",",A10)+2)))
      looks for the commas and uses mid to select the text between them.
      Assumes text is in A10.

  66. I have a long column of number letter combinations I want to split at the first letter (any letter)

    eg. 43245tre becomes 43245 tre
    129ftr becomes 129 ftr

    Help?

    1. Hello Mark,

      Do you want to split numbers and letters in 2 separate columns, or separate them with a space within a cell?

  67. what if we want to split my string e.g. or and. How do you specify split only when or or and is byitself and not part of a word. The below keeps happening.

    dog or cat slit into rows dog
    cat
    orange and apple or
    ange
    apple

    1. Hi Lila,

      You can use the 'Split by string' option of our add-in. When entering the delimiter strings, just be sure to type a space before and after or to prevent splitting words.

      And thank you for this great question! I will certainly include this tip in the article.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)