How to convert one cell by column using one delimiter and by row using another delimiter?

It imports as one long string as it's not officially a CSV file.
CSV - comma separated variable. The commas indicate where to separate the data.

But after each 0 is a space which is clearly indicating the end of data and the start of new data.
CSV files dont use space as a separator, therefore it's not a CSV file and Excel is interpreting it all as a string.

You prob need VBA to do this properly, unfortunately I dont have time.

You CAN do this with a mix of Global replacements, Text to Columns, Copy and Transpose, and Text to Columns. That's the good news. The bad news is that, for some reason (probably too many digits after the decimal place), it loses the last digit. I'm guessing that is important .

So, as Special-K has said, think it would need VBA. BUT, you might suffer with the same issues.

you could try inserting a basic Power Query script along lines of attached (assumes .txt)

Please Login or Register  to view this content.

to modify for your own use, with Queries and Connections panel, right click - Edit - then update the file path in Source step by clicking on the first Settings icon, and Browsing to the location

edit: per TMS, removed Change Type as you will need to persist as Text given XL limits on significant digits

Please note that John Topley's VBA routine is much quicker than the manual approach I outlined earlier. However, it still loses the least significant digit. For example, -149.9206228808253,61.26921241825455 splits as -149.920622880825 and 61.2692124182545. I ask again, is that an issue?

Slight modification to JT's code. Definitely text output BUT it does retain all the digits.

Please Login or Register  to view this content.

Data in A1

B1
=TRIM(MID(SUBSTITUTE(FILTERXML("<x><m>"&SUBSTITUTE(A1,",0 ","</m><m>")&"</m></x>","//m"),",",REPT(" ",40)),{1,41},40))

You're welcome.

Have a good weekend.

@JohnTopley:

but has already been pointed, data will be TEXT

Actually, I think pushing the array out to the worksheet appears to output numeric data. Hence the tiny mod to your code.



@akedm:
If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.