1
mozilla-nss-3.11.4-0.7

gdb-10.12-1.5.2

glibc-dcc-atv-1.0.3-10.6

i want to separate it too in the next B C D cell

mozilla-nss      3.11.4       0.7

gdb              10.12        1.5.2

glibc-dcc-atv    1.0.3        10.6

right now i can use left , right and find function to do it but not quite work well

i use 

LEFT(B33,FIND(".",B33)-2) =B cell

RIGHT(B33,FIND(".",B33))   =C Cell

RIGHT(D33,FIND("-",D33)-1)  = D Cell

answer is not right anyone can Help me correct my function thank you

eathapeking
  • 329
  • 2
  • 6
  • 17
  • 1
    I'm not sure you'll find an easy way to do this in Excel. It wouldn't be terribly difficult except that the package names can also have hyphens. If you're doing this as part of a one-time conversion, I'd suggest doing the split with your favorite programming language. – Dan Jan 21 '13 at 18:48
  • 1
    Agree with Point 1 given by @PowerUser. But after that you don't need formulas. You can use Text To Columns ;) – Siddharth Rout Jan 21 '13 at 19:19
  • i cant use text to column ....becuase if something like :::: mozilla-nss-3.11.4-0.7:::: gdb-10.12-1.5.2 appear you can see it dont have same number of "-" – eathapeking Jan 22 '13 at 02:19
  • @SiddharthRout, good point. I didn't think to use that. – PowerUser Jan 23 '13 at 16:30
  • There is a power query option for this check this one out. https://stackoverflow.com/questions/72210897/excel-how-to-split-cells-by-comma-delimiter-into-new-cells – Shane S Jan 06 '23 at 07:30

2 Answers2

2

Here is a less than perfect solution:

  1. Do a search & replace to get rid of any dashes that are not delimiters. For example, replace "mozilla-nss" with "mozillanss"
  2. Put your values in Column A starting at A1
  3. In B1, enter =LEFT(A1,FIND("-",A1)-1)
  4. In C1, enter =SUBSTITUTE(A1,B1,"")
  5. In D1, enter =SUBSTITUTE(LEFT(C1,FIND("-",C1,2)),"-","")
  6. In E1, enter =SUBSTITUTE(SUBSTITUTE(C1,D1,""),"-","")
  7. Fill Down the equations for all your values in Column A.
    Edit: Added next line:
  8. Replace "mozillanss" with mozilla-nss".

Your answers are in columns B,D, and E.

PowerUser
  • 11,583
  • 20
  • 64
  • 98
2

The key point here which makes the task difficult - we need to use as separators LAST TWO hyphens in the string, and remain all the rest intact. For such cases ARRAY formulas is the best shot. My solution is below:

  1. Name 6 columns starting A1: String | MAX "-" | 2nd MAX "-" | Str1 | Str2 | Str3
  2. Put your values in Column A starting at A2.
  3. B2 (MAX "-"): type the formula =MAX(IFERROR(SEARCH("-",$A2,ROW(INDIRECT("1:"&LEN($A2)))),0)) but press CTRL+SHIFT+ENTER instead of usual ENTER - this will define an ARRAY formula and will result in {} brackets around it (but do NOT type them manually!).
  4. C2 (2nd MAX "-"): type the formula =MAX(IFERROR(SEARCH("-",$A2,ROW(INDIRECT("1:"&LEN($A2)))),0)*IF(IFERROR(SEARCH("-",$A2,ROW(INDIRECT("1:"&LEN($A2)))),0)=MAX(IFERROR(SEARCH("-",$A2,ROW(INDIRECT("1:"&LEN($A2)))),0)),0,1)) and again press CTRL+SHIFT+ENTER.

Thus we'll obtain positions of LAST TWO hyphens in the string. The rest is easy - ordinary LEFT / MID / RIGHT stuff:

  1. D2: =LEFT($A2,$C2-1), ENTER.
  2. E2: =MID($A2,$C2+1,$B2-$C2-1), ENTER.
  3. F2: =RIGHT($A2,LEN($A2)-$B2), ENTER.
  4. Autofill B:F.

If temporary columns B:C are unwanted - you should replace references to them in D:F for B:C contents (i.e. replace $A2 in =LEFT($A2, with A2 actual formula), but this will result in TOO complicated ARRAY formulas, still doing their job - but difficult to understand the next day even for the creator)

As for the above solution - perhaps it might be improved or simplified, but I'm pretty much familiar with such ROW...INDIRECT constructions from times I had to analyze megabytes of statistic data, so for me it's just as easy as create LEFT / RIGHT. Anyway, it seems to work.

For your convenience my sample file is shared: https://www.dropbox.com/s/p49x32t3a0igtby/StringHyphensSeparate.xlsx

Hope that was helpful)

ADDITION - 2 more simplified solutions to find LAST TWO hyphens (the rest of steps is the same as above):

  • More simple ARRAY formulas:

    • B2 (MAX "-"): type the formula =MAX(IF(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)="-",ROW(INDIRECT("1:"&LEN($A2))),0)) but press CTRL+SHIFT+ENTER instead of usual ENTER - this will define an ARRAY formula and will result in {} brackets around it (but do NOT type them manually!).
    • C2 (2nd MAX "-"): type the formula =LARGE(IF(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)="-",ROW(INDIRECT("1:"&LEN($A2))),0),2) and again press CTRL+SHIFT+ENTER.

  • Regular formulas using SUBSTITUTE function:

    • B2 (MAX "-"): type the formula =SEARCH("@",SUBSTITUTE($A2,"-","@",LEN($A2)-LEN(SUBSTITUTE($A2,"-","")))), ENTER.
    • C2 (2nd MAX "-"): type the formula =SEARCH("@",SUBSTITUTE($A2,"-","@",LEN($A2)-LEN(SUBSTITUTE($A2,"-",""))-1)), ENTER.

The key for SUBSTITUTE solution is that it may replace only certain instances of matches, i.e. only 2nd or 3rd hyphen. The overall number of hyphens is determined again via SUBSTITUTE formula: length of original string MINUS length of string with ALL hyphens replaced to empty strings: LEN($A2)-LEN(SUBSTITUTE($A2,"-",""). One more trick here - while we should remain the original string intact, we still MAY do anything with it for intermediate solutions! Thus, we replace the hyphen with @, and then search for @ in temporary string.

All the above solutions are working, choose what you like / understand better. Hope that will also help in understanding array formulas, since for the same task there are 2 different approaches.

I updated the example file to include the last 2 examples + resulting megaformulas without intermediate steps, link is the same and located above. Good luck!

Peter L.
  • 7,276
  • 5
  • 34
  • 53
  • 1
    While having tea I came to simpler solution, though still using ARRAY formulas, but much shorter that time. Reply back if interested) – Peter L. Jan 22 '13 at 08:48
  • 1
    @PowerUser yeah, looks nice) BTW I noticed that about a week ago in one post here on Stack. – Peter L. Jan 22 '13 at 16:24
  • Thank Youuuuuuuuuuuuuuuu It's toooooooooo helpful from youuuu :D – eathapeking Jan 23 '13 at 03:38
  • how long before you know this much. i'm just a student right now. i think someday i'll be skill enough... but it took time and effort T^T – eathapeking Jan 23 '13 at 03:47
  • 1
    @eathapeking I updated the answer & sample file with 2 more, less complicated approaches, one of them even use regular formulas. Hope that will help in your excel learning) Enjoy! – Peter L. Jan 23 '13 at 06:37