Extracting string from between commas

[Kasper Madsen]

Hello everyone!

I have been using loads of good advice from this forum by reading about other people's Excel problems - and now I've arrived at a problem I can't seem to find a previous solution to:

I have a column of data, with each cell containing from 0 to 6 strings, separated by ", " (a comma and a space).

I need to extract each of the words between the commas and put one each into the following 6 columns in my spreadsheet (basically the same way as the 'Text to Columns'-button, but this NEEDS to be automated).

I have attempted something along the lines of inserting this formula in each of the following columns:

Column 1
=IF.ERROR(MID(A2,1,(FIND(",",A2,1)-1)),"")

Column 2
=IF.ERROR(MID(A2;FIND(",";A2;FIND(",";A2;1))+2;FIND(",";A2)-1);"")

...


But obviously this only works if the strings are all the same length (which they are not).

Can anyone help me arrive at a solution? Any assistance will be greatly appreciated!


Sample data:

A2
Svanemærke, Astma, Øko-Tex

A3
Svanemærke

A4
Svanemærke, Øko-Tex

 

[Gerald Higgins]

But obviously this only works if the strings are all the same length

Testing your formulas suggests that the above is not true.

For example here are the results I get.
A.........B.........C
a, b......a.........b
ab, cd...ab.......cd

Actual formulas used:
B2

=IFERROR(MID(A2,1,(FIND(",",A2,1)-1)),"")

and C2

=IFERROR(MID(A2,FIND(",",A2,FIND(",",A2,1))+2,FIND(",",A2)-1),"")

Remember my regional settings seem to be different from your's.

ps - welcome to the board !

 

[wigi]

Hello

Welcome!

You coulmd use Data > Text to columns, but in an automated way using only a limlited number of code lines.

Here's code that does just that: http://www.mrexcel.com/forum/showthread.php?t=584610

 

[Kasper Madsen]

Hello there, and thank you for your replies!

@Gerald: What I meant about strings being the same length was that if the text before the first ", " is say 4 characters long, and the text after the ", " is say 8 characters long, then that text will be cut off.

@wigi: Thanks for the link - I'll try to see if I can make it work! (I haven't tried working with code in Excel yet, so am sure it will be a challenge!)

If anyone thinks of a good solution to my problem using formulas meanwhile please do let me know.

And thanks for any help - it's greatly appreciated!

 

[Kasper Madsen]

Alright I tried using the code you suggested:


Sub splitit() With Selection .Replace ",", "§", xlPart .TextToColumns other:=True, OtherChar:="§" End WithEnd Sub</PRE>
And I am able to kinda make it work.

I have a few questions though:

- This only works with the current selection - how can I make it work on 'Column O' instead?

- Is there a way to call this code from the cell just like a formula?

- If it isn't possible to call it from a cell, would it be possible to call it when the document is opened (but AFTER other formulas are in effect, since the data I am trying to split into columns is being returned from another formula)

Thanks a lot in advance.

 

[wigi]

Hello

1. Use

Sub splitit()
    With Range("O2", Range("O" & Rows.Count).End(xlUp))
        .Replace ",", "§", xlPart
        .TextToColumns other:=True, OtherChar:="§"
    End With
End Sub

This code will act on the sheet that is opened just at the time of the Workbook_Open event. See below.

If that active sheet is not the correct one, then add the sheet name to the range of column O above.

2. Add to the ThisWorkbook module code:

Private Sub Workbook_Open()
    splitit
End Sub

 

[Kasper Madsen]

Hello, and thanks again for the reply!

Apparently, it seems that the code suggested above (and indeed the Text to Columns function in general) doesn't work on text returned by a VLOOKUP-formula?

I have uploaded the document I am working on, maybe this will make it easier to give me hints on how to solve the problem:

http://www.abena.dk/Files/@saekko.dk/files/Copy of Katalogfremstilling MASTER.xlsx

The data I am working on is located in the sheet called 'MELLEMREGNING', columns O to U.

 

[Gerald Higgins]

@Gerald: What I meant about strings being the same length was that if the text before the first ", " is say 4 characters long, and the text after the ", " is say 8 characters long, then that text will be cut off.

Isn't that what you want ?

It might be helpful to give us a couple more examples showing input and required output.

Edit to add, sorry I'm not opening that file

 

[Kasper Madsen]

Hello again!

Isn't that what you want ?

It might be helpful to give us a couple more examples showing input and required output.

Here's an example of what I want to get:

A -------------------------B ----------C -------------D

text1, longtext2, text3 -----text1 ------longtext2 -----text3


Here's an example of what I am actually getting using the fomula I wrote:


A -------------------------B ----------C --------------D

text1, longtext2, text3-----text1 -------longt -----------text3


As you can see, the problem lies in Column C, where the text is getting cut off at the number of characters of the preceeding text string.

So basically my problem is finding a way to supply the [number of characters]-argument of the MID-formula with a number that equals the length of the text string following the next comma in each column. I am at a loss as to how I can do this!

 

[energman58]

Here is a formula solution

You can obviously do the first one and this formula which is designed to sit in column d and copied to the right will find the second, third etc. if they exist

If you dont want the second ocurrance to be returned in D you need to adjust the Column()-3 etc terms appropriately (if it is C you want you reduce 3 to 2 etc)


=IFERROR(MID($A2,SEARCH(CHAR(127),SUBSTITUTE($A2,", ",CHAR(127),COLUMN()-3)&CHAR(127))+2,(SEARCH(CHAR(127),SUBSTITUTE($A2,", ",CHAR(127),COLUMN()-2)&CHAR(127)))-(SEARCH(CHAR(127),SUBSTITUTE($A2,", ",CHAR(127),COLUMN()-3)&CHAR(127)))-2),"")


I am sure there is a clever way of making it completely general so it gets teh first one too but I cant be bothered to work it out!