Extracting string from between commas

K

Kasper Madsen

New Member
Joined
Oct 21, 2011
Messages
9
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
But obviously this only works if the strings are all the same length
Click to expand...

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
Code: 
=IFERROR(MID(A2,1,(FIND(",",A2,1)-1)),"")
and C2
Code: 
=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 !
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
Hello

1. Use

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

Code: 
Private Sub Workbook_Open()
    splitit
End Sub
 
Upvote 0
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.
 
Upvote 0
Kasper Madsen said:
@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.
Click to expand...

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 :)
 
Upvote 0
Hello again!

Isn't that what you want ?

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

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! :)
 
Last edited:
Upvote 0
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!:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Extract the characters between 2 spaces in a string
Replies
5
Views
162
Fluff
Fluff
T
  • Solved
Challenge Question: A Quest for Elegance
Replies
6
Views
254
Peter_SSs
Peter_SSs
T
  • Solved
pivot table from columns with strings separated by commas
Replies
7
Views
299
Fluff
Fluff
2BLUE4U
  • Solved
Extracting Numbers from Text String
2
Replies
10
Views
797
2BLUE4U
2BLUE4U
H
  • Question
data extract between sheets For the value entered
Replies
1
Views
136
LazyBug
LazyBug

Forum statistics

Threads
1,214,608
Messages
6,120,500
Members
448,968
Latest member
screechyboy79

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top