Share via

Workaround with CSV with several columns and text to column replacing data

Anonymous
2024-01-03T20:52:22+00:00

Dear community,

I face the issue that some of the software we use has exports where the data in the csv file is broken into several columns when I open it in Excel. However, most of the content is still in "regular" CSV format where the different columns are all shown in one column, separated by a comma. However, when I use the text-to-column feature, it

  1. either warns me that data would be replaced,
  2. an alternative idea would be to select all columns at the same time but it is not possible to use the text-to-columns on several columns at the same time

Is there an alternative workaround so that I do not lose data while also having all columns mapped correctly? Google Sheets automatically maps all columns correctly when importing csv files.

Many thanks!

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-01-04T01:54:16+00:00

    You may try power query import wizard.

    1. Data>get data>from file > from CSV

    1. choose your delimiter.

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2024-01-11T12:00:52+00:00

    Thank you! This was an interesting workaround but it also breaks the decimal delimiters in the source data.

    Thus, it unfortunately does not help for my use case.

    1 person found this answer helpful.
    0 comments
  3. Rich~M 20,370 Reputation points Volunteer Moderator
    2024-01-11T22:35:56+00:00

    Hi exceluser. I am an Excel user like you.

    When you use Text to Columns Excel will not insert additional columns for the data that is being split up. So, if you are breaking the data up into three columns and there is other data already in the two columns to the right of the one you are expanding, Excel will overwrite those two columns which is why you get the warning. If you add blank columns for the data to expand into (figure how many extra columns in addition to the original column that it will take) then Excel will expand into those blank columns where it won't overwrite any data and you shouldn't get that warning.

    0 comments
  4. Anonymous
    2024-01-11T15:56:28+00:00
    0 comments