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

 

[nicholaspars]

You are welcome. Thank you for your feedback and we'll see you next time.

It appears that I need some further help with this. I am using the formula to extract data from csv file via WEBSERVICE. The formulas provided work well, however the csv is actually returning a 100 row (last 100 stock prices quotes) string. I am therefore retrieving the correct data from the 100th row, rather than the second row. The data string below illustrates what I am attempting to retrieve in red.

timestamp,open,high,low,close,volume
2017-11-07,3.3900,3.4300,3.3400,3.4000,4604087
2017-11-06,3.3500,3.4500,3.2900,3.4000,6117028
2017-11-03,3.3800,3.4100,3.2600,3.2900,8962723...
...100 rows of data

How can I adapt previously provided formulae to account for this much longer string? i.e. Return the number after the ninth comma from the left.

Thanks for your input.

 

[Peter_SSs]

How can I adapt previously provided formulae to account for this much longer string? i.e. Return the number after the ninth comma from the left.

Try

=MID(SUBSTITUTE(A1,",",REPT(" ",100)),800,100)+0

 

[Rick Rothstein]

Try

=MID(SUBSTITUTE(A1,",",REPT(" ",100)),800,100)+0

Nicholas asked to return "the number after the ninth comma from the left so your 800 should be 900. Now you may turn out that you are correct and Nicholas might have meant "ninth field from the left" so I guess we'll have to wait for him to return to this thread to clarify that for us.

 

[Peter_SSs]

Nicholas asked to return "the number after the ninth comma from the left so your 800 should be 900. Now you may turn out that you are correct and Nicholas might have meant "ninth field from the left" so I guess we'll have to wait for him to return to this thread to clarify that for us.

No, it was a typo on my part Rick. Thanks for picking it up. The item to be extracted was clearly identified & I meant to type the following formula.

=MID(SUBSTITUTE(A1,",",REPT(" ",100)),900,100)+0

 

[nicholaspars]

No, it was a typo on my part Rick. Thanks for picking it up. The item to be extracted was clearly identified & I meant to type the following formula.

=MID(SUBSTITUTE(A1,",",REPT(" ",100)),900,100)+0

Hi Peter

Yes the recent formula works if the string is on the worksheet, and I note that I can "move" the required comma value by changing the 800 to 900 etc.

The issue now is that the most recent formula returns an error when used with WEBSERVICE, however the formula provided in post #28 does in fact return a number, albeit the number from row 100 of the csv.

If you have the time to look at this here are the 2 formulae. I have generated a temporary API key which is embedded in the formulae which you are free to use.

If you would like to take a look at the csv output on its own simply type https://www.alphavantage.co/query?f...&datatype=csv&symbol=GOOGL&outputsize=compact into any web browser (for stock price of GOOGL)

To retrieve a stock quote from excel using the formulae provided, simply put any stock ticker in cell A1 e.g. GOOGL

the first formula is:

=ROUND(LEFT(RIGHT(SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&interval=1min&apikey=H0X1I3P1VYFW0VZ0&datatype=csv&symbol="&A1&"&outputsize=compact"),",",REPT(" ",50)),100),50),105) which works

the second becomes:

=MID(SUBSTITUTE(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&interval=1min&apikey=H0X1I3P1VYFW0VZ0&datatype=csv&symbol="&A1&"&outputsize=compact"),",",REPT(" ",100)),900,100)+0

I'm really at a loss as to why the 1st formulas returns a number, but the second an error??

Thanks

 

[Peter_SSs]

I'm really at a loss as to why the 1st formulas returns a number, but the second an error??

I think the string manipulations got a bit long.
Try this modification.

=MID(SUBSTITUTE(LEFT(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&interval=1min&apikey=H0X1I3P1VYFW0VZ0&datatype=csv&symbol="&A1&"&outputsize=compact"),200),",",REPT(" ",100)),900,100)+0

 

[nicholaspars]

I think the string manipulations got a bit long.
Try this modification.

=MID(SUBSTITUTE(LEFT(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&interval=1min&apikey=H0X1I3P1VYFW0VZ0&datatype=csv&symbol="&A1&"&outputsize=compact"),200),",",REPT(" ",100)),900,100)+0

Peter the new formula works perfectly. I can't thank you enough, and also thanks to Rick for his input.

 

[Peter_SSs]

Peter the new formula works perfectly. I can't thank you enough,..

Good news. You just did..