String Separate in Excel

Question

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

Answer 1

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!

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