Obtain numbers from a column of data

[Mkkl]

Hey there,

I have the following question. I have a column filled with the following data

2014-01-26,"215.6044","7,036.1643",,,
2014-01-19,"215.1711","7,052.2114",,,
2014-01-12,"214.7129","7,046.8200",,,
2014-01-05,"217.2313","7,100.4086",,,
2013-12-29,"217.5037","7,091.5871",,,

Now I would like to extract the numbers between " " and store them in two separate columns.
Is this possible and could someone help me with this?

Kind regards,

Michiel

[:) Sixthsense :)]

Use Text To columns

Select the Data>>Alt+D+E>>Select Delimited>>Next>>Give Tick on Other checkbox alone>>Enter " (Single Quote in the textbox)>>Next>>Finish

[Norie]

Select the column, goto Data>Text to columns, selected Delimited, select comma as the delimiter and click Finish.

[kbhandari]

Hi! Michiel,

Please replace "," with "blank" first then use text to columns for seperating your data.

Regards

[Mkkl]

Thank you all really much for your quick responses.
However the last number is in the form #,###.## where the comma separates the thousands from the hundreds and the . the hundreds from the decimals.
So I get these numbers in two separate columns because of the comma separator. How can I add these two columns together again?

[Norie]

On the 3rd step click Advanced and swap around the decimal and hundreds separators.

[:) Sixthsense :)]

Have you tried the " ?

[Mkkl]

I already got it! I just divided the first numbers by 10000 since the comma's were messed up. And for the second set of numbers I divided by 10000 and added the 7s and 6s times a 1000. Thanks for your replies!!

[:) Sixthsense :)]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.