Forum Discussion

Copper Contributor

Sep 28, 2019

Solved

Need help removing wrap text from 3 cells, not as easy as it sounds

A customer sent me an excel file that I need to save as a CSV so it can be loaded into a system. When I open the document as a CSV file cells B2, B3, and B4 are showing blank, however if you expand t...

excel

Subodh_Tiwari_sktneer
Sep 28, 2019
penguinapplesauce
You may try something like this and see if that resolves the issue.
In a blank column, in Row2, place this formula...
=TRIM(SUBSTITUTE(B2,CHAR(10)," "))
and then copy it down to row4.
Then copy the three formula cells, select the cell B2 and paste it as Values only and delete the formula cells. That should replace all the line breaks in B2:B4 with space and trim them in the end to remove any leading or trailing spaces.

Subodh_Tiwari_sktneer

Silver Contributor

Sep 28, 2019

penguinapplesauce

You may try something like this and see if that resolves the issue.

In a blank column, in Row2, place this formula...

=TRIM(SUBSTITUTE(B2,CHAR(10)," "))

and then copy it down to row4.

Then copy the three formula cells, select the cell B2 and paste it as Values only and delete the formula cells. That should replace all the line breaks in B2:B4 with space and trim them in the end to remove any leading or trailing spaces.

ramh_84

Copper Contributor

Sep 01, 2023

Subodh_Tiwari_sktneer

Thanks so much for this working solution!

My question to you is how to put such barrier in place when using Power BI?

How to mimic your solution to automatically take into account such unwanted wrapped cells that can be found as part of CSV files?

I have tried to trim the data in BI file assuming that it will take care of this wrapping, but it didn't work.

Appreciate if you have a suggestion for above issue.