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 6. Total comments: 306

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

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

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

  4. Source is like this...
    P. Umesh, 123 ramesh, 34 p. M. Kavya, 10,55
    Out put
    P. Umesh 123
    Ramesh 34
    P.m.kavya 10
    P. M. Kavya 55

    Could plz me this problem...

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

  6. In an XL spread sheet - column "?" I'd like to split rows #25, 26, 27, 28, 29 & 30 BUT not add an additional column. These rows are situated partway down an XL spreadsheet and I cannot distrurb any columns or rows around this particular block.
    Can this be accomplished?
    E.g. - in each of the the cells:
    Data 21
    Data 22
    Data 23 As per above, I'd like to keep the column and cells as is but have them "split" between "Data and the number value.

    Appreciate any help you may have~!

  7. Hi,
    Please advise a formula for the below sample data
    "Apple 8Ap #556-1337 Ali Avenue
    Galaxy S823433 Eet, Av.
    Google Pixel 2P.O. Box e446,Justo Av"
    In the above lines i want to split Device and Address in two different cells using formulae.
    For Eg: In Column 'A' we have "Apple 8Ap #556-1337 Ali Avenue", then using a formula i require
    Device should be extracted in column B as "Apple 8"
    Similarly, Address should be extracted in Column C "Ap #556-1337 Ali Avenue"

    Note: I have 1000 different devices, where i should not entry any data manually.
    Kindly help me with this.

  8. Hi,
    I have a huge data file in this text format:

    "0009_000006-1","Camera Equipment"
    "2/1/2018","2/28/2018","$39.83",""
    "3/1/2018","3/31/2018","$39.83",""
    "4/1/2018","4/30/2018","$39.83",""
    "5/1/2018","5/31/2018","$39.83",""
    "6/1/2018","6/30/2018","$39.83",""
    "7/1/2018","7/31/2018","$39.83",""
    "8/1/2018","8/31/2018","$39.83",""
    "9/1/2018","9/30/2018","$39.83",""
    "10/1/2018","10/31/2018","$39.83",""
    "11/1/2018","11/30/2018","$39.88",""
    "--------------------"
    "Total for","2018",":","$398.35",""
    "===================="
    "--------------------"
    "Totals for Asset:","0009_000006-1"
    "Projection Grand Total:","$398.35"
    "Pre-","Projection Grand Total:","$0.00"
    "===================="
    "0009_000025-1","34801 HP Elitebook 840G1"
    "2/1/2018","2/28/2018","$42.10",""
    "3/1/2018","3/31/2018","$42.10",""
    "4/1/2018","4/30/2018","$42.10",""
    "5/1/2018","5/31/2018","$42.10",""
    "6/1/2018","6/30/2018","$42.10",""
    "--------------------"
    "Total for","2018",":","$210.50",""
    "===================="
    How can I convert this huge data file to excel format so I can create pivot table:
    Asset ID Asset Class Begin Date End Date Amount
    0009_000006-1 Camera Equipment 2/1/2018 2/28/2018 $39.83
    0009_000006-1 Camera Equipment 3/1/2018 3/31/2018 $39.83
    0009_000006-1 Camera Equipment 4/1/2018 4/30/2018 $39.83
    0009_000006-1 Camera Equipment 5/1/2018 5/31/2018 $39.83
    0009_000006-1 Camera Equipment 6/1/2018 6/30/2018 $39.83
    0009_000006-1 Camera Equipment 7/1/2018 7/31/2018 $39.83
    0009_000006-1 Camera Equipment 8/1/2018 8/31/2018 $39.83
    0009_000006-1 Camera Equipment 9/1/2018 9/30/2018 $39.83
    0009_000006-1 Camera Equipment 10/1/2018 10/31/2018 $39.83
    0009_000006-1 Camera Equipment 11/1/2018 11/30/2018 $39.88
    0009_000025-1 34801 HP Elitebook 840G1 2/1/2018 2/28/2018 $42.10
    0009_000025-1 34801 HP Elitebook 840G1 3/1/2018 3/31/2018 $42.10
    0009_000025-1 34801 HP Elitebook 840G1 4/1/2018 4/30/2018 $42.10
    0009_000025-1 34801 HP Elitebook 840G1 5/1/2018 5/31/2018 $42.10
    0009_000025-1 34801 HP Elitebook 840G1 6/1/2018 6/30/2018 $42.10
    It would help me a deal if you can assist. Thank you!

    1. Hi Lynn, is your problem solved or I can help you?

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

  10. Hi there,
    I want to split text from a specific word. Would you please anyone share the formula.
    The raw date are given below.
    CLS1103 EIDF19 DUSTY BLUE
    CLSMK09 EIDF19 60E MIDNIGH
    What I want split from Eidf19. So cls1103 eidf19 is 1st column and 2nd column is dusty blue

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

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

  13. Is it just the way the syntax was created?

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

  15. In example #2 above, can you explain the +1 when finding the difference between the 2nd and 1st hyphens and how does the last search argument explains how the function understands how to extract all the characters between the two hyphens?

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

  17. Hi , thanks a lot for this, but i have a big problem , it's only for 3 column, i am working as data entry and i have 10 column , hot to do this please help

  18. Gemcitabine_pow_1000 mg_1 X 1V
    Ranitidine_tab MR_150 mg_6 X 10T
    Ranitidine_tab MR_300 mg_3 X 10T

    Above text please separate from "_" (Only by formula) , Not by text to column

    Please send me formula

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

  20. i have data in excel

    A battery consists of (a) only one cell (b) only two cells (c) two or more cells (d) All of the above

    Column A Column B Column C Column D Column E

    i need to separate Data for example

    1. Before (a) Column A
    2. After (a) and Before (b) Column B
    3. After (b) and Before (c) Column C
    4. After (C) and Before (D) Column D
    5. After (D) Column E

    pls help

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

  22. Arvind:
    To split this string I would use the Text-to-Column tool.
    You can find it under the data tab. Use the fixed width option and after you enter the comma as a field choose the Do Not Import option for the comma.

  23. I want to split this
    Sr. N0 first name last name postal code

    1 jokovic,novok (SRB) 13630

    HOW ?

  24. String is $GPRMC,062745.044,A,1830.2470,N,07350.5297,E,1.07,44.40,101018,,,A*54

    how to split into columns with removal of comma with formula.

    ex-
    1st column=$GPRMC
    2ND COLUMN=062745.044
    3RD COLUMN=A
    4TH COLUMN=1830.2470
    5TH COLUMN=N
    6TH COLUMN=07350.5297
    WHAT FORMULA SHOULD I USE. I need only formula or macro code not other ready made option in excel.

    Thanks

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

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

  27. Formulas for splitting text string with spaces into columns worked perfectly.
    Thank you.

  28. Hi
    Irregular word and Number in excel 1 column to multi column like
    Example:JSE SALE 2800 DRS CASH 2815
    1. words in 1 column
    2. Numbers in 1 column

  29. Clarence:
    I can't tell if there is a space between the first decimal and the "1". If there isn't, I think this will work for you. Where the original string is in K1 enter this in an empty cell.

    =MID(K1,1,3)&" "&MID(K1,5,2)&""&MID(K1,7,4)&""&MID(K1,11,3)&" "&MID(K1,15,3)&""&MID(K1,18,7)&" "&MID(K1,25,7)
    I hope you see how this formula works. It counts the characters including spaces goes to the next number of characters indicated by the second number and then returns that number of characters followed by a space or in some cases no space if there already was a space in the original. I got tired of counting characters.
    If you need a space after the first decimal add another &""&MID(K1,etc)
    like this:
    =MID(K1,1,3)&" "&MID(K1,5,2)&""&MID(K1,7,4)&""&MID(K1,11,3)&" "&MID(K1,15,3)&""&MID(K1,18,4)&" "&MID(K1,22,3)&" "&MID(K1,25,7)

  30. 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)

  31. I've been trying the solutions here to no avail.
    I am trying to get an input into a cell visually altered inside the same cell

    From, when numbers are entered:
    01234567890
    To, when the cell is left:
    012 3456 7890

    Anyone got any ideas

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

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

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

  35. One of the column has the value "Fri 6pm PT", I need this value to be split into 3 different columns and also need improvisations like once the column is split the "fri" column should be "Friday" and the column with "6pm" should be "6:00 PM". I need the formula on how to change.

  36. 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)

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

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

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

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

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

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

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

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

  45. i want to spit one cell number 12345

  46. My electrical engineer son-in-law is giving us a hint as to what he and my daughter are naming my grandson-to-be. Here is the strand he gave us ^[B-P][a,e,l,o,u]\w{3,} . He then added, "It's a string search pattern. It can be reverse engineered. Can anybody solve this for me?

  47. Hi,

    Can you help me splitting the word astro.forumattivo.com to astro and forumattivo.com

    thanks!
    skuty

  48. 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,"")

  49. Hi,
    Can you please help to seperate only the colours from the cell into different column.
    For eg.
    From PVC SLEEV 65X6 HEAVY DUTY BLACK COLOUR.Only the Black into different column.
    Like wise in given below contents:-
    PVC SLEEV 65X6 HEAVY DUTY BLACK COLOUR.
    PVC CISTERN WHITE
    PVC CISTERN-WHITE
    PVC CISTERN -WHITE

  50. Is it possible to slice Inputsome into various cells(Crore, Lac, Thousand, hundreds)?

    Inputsome Crore Lac Thousand Hundred
    1091050320 9 10 50 320
    173387930 17 33 87 930
    720333 7 20 333
    1209225 12 9 225
    3209 3 209
    16305 16 305
    502 502
    50 50
    9 9

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