Formula For String Separation with Specific Pattern

Question

This is my worst case scenario for my work. I have to auto-separate this set of strings, but just don't know which function I should use. This is totally difficult! I need to separate the strings for the next step of my database work in Excel.

Here are examples of the data:

The strings to be separated in column A --

java-1.6.0-openjdk-1.6.0.0-1.21.b17.el6.x86_64
java-utils-1.6.0.0-1.el6.x386 
Java-1.7.0-openjdk-1.7.1-3.1.5
gdb-1.6-5.2.7.el6

The parts of the strings after being separated successfully, in columns B C D E F G H I --

#         name        #         Version    # OSLv # Architecture #
java # 1.6.0 #openjdk # 1.6.0.0 # 1.21.b17 # el6  # x86_64 #
java#  utils#         # 1.6.0.0 # 1         #el6_3 #x386  #
Java#       #         # 1.7.1   #   3.1.5  #      #      #
gdb #       #         # 1.6     #5.2.7     #el6   #      #

(In my opinion, if we separate the strings into two parts first, like

java-1.6.0-openjdk-1.6.0.0-1.21.b17     #####     .el6.x86_64

then the split into specific parts later will be easier...maybe !?)

Thank you a lot for every bit of help.

I will accept any solutions that you can provide me for later study :D

"#" marks show the separation of the parts of the strings into different cells.

zero knowledge about JQuery but thank you i will study about it after this — eathapeking, Feb 14 '13 at 02:50

score 2 · Accepted Answer · answered Feb 13 '13 at 07:01

2

The following formulas are not a complete solution, but should provide a starting place for you to understand the kind of string manipulations that you will need to do:

From the head of the string to be parsed:

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

  =IFERROR(MID(A1,LEN(A6)+2,SEARCH("-",A1,LEN(A6)+2)-LEN(A6)-2),"-")

  =IFERROR(MID(A1,LEN(A6&B6)+3,SEARCH("-",A1,LEN(A6&B6)+3)-LEN(A6&B6)-3),"-")

  =IFERROR(MID(A1,LEN(A6&B6&C6)+4,SEARCH("-",A1,LEN(A6&B6&C6)+5)-LEN(A6&B6&C6)-4),"-")

(I've left a few in the middle of the string for you to do.)

From the tail of the string to be parsed:

  =IFERROR(MID(A1,SEARCH("el",$A1),SEARCH("x",A1)-(SEARCH("el",A1)+1)),"-")

  =IFERROR(RIGHT(A1,LEN(A1)-SEARCH("x",A1)+1),"-")

The fact that the strings don't have the same number of elements and that not all the elements are separated by dashes ("-") makes this more complicated than the usual string parsing task. When you get stuck, you should come back with a more specific question.

answered Feb 13 '13 at 07:01

chuff

5,846
1
21
26

yes the problem is if all element separate by - dash it is a lot easier.. thank you a lot :) – eathapeking Feb 13 '13 at 08:52
2

Look this thread as well: http://stackoverflow.com/a/14453972/1953175 - it's almost the same trouble was solved – Peter L. Feb 13 '13 at 09:29
Yes Yes by your mention that previous question i got a new idea thank you thank you > – eathapeking Feb 13 '13 at 09:52
@eathapeking good luck pal, glad our community suggestions were helpful! – Ksenia Feb 13 '13 at 10:46

score 2 · Answer 2 · answered Feb 13 '13 at 11:46

2

For really complicated string extractions I'd be tempted to go for a Regular Expression in a worksheet function.

See http://excelicious.wordpress.com/code-samples/vba/regex-functions/ for a library which you can build with/on.

answered Feb 13 '13 at 11:46

James Snell

604
4
13

Formula For String Separation with Specific Pattern

2 Answers2