SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-880395%22%20slang%3D%22en-US%22%3ENeed%20help%20removing%20wrap%20text%20from%203%20cells%2C%20not%20as%20easy%20as%20it%20sounds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880395%22%20slang%3D%22en-US%22%3E%3CP%3EA%20customer%20sent%20me%20an%20excel%20file%20that%20I%20need%20to%20save%20as%20a%20CSV%20so%20it%20can%20be%20loaded%20into%20a%20system.%20When%20I%20open%20the%20document%20as%20a%20CSV%20file%20cells%20B2%2C%20B3%2C%20and%20B4%20are%20showing%20blank%2C%20however%20if%20you%20expand%20the%20column%20or%20remove%20the%20wrap%20text%20selection%20the%20value%20you%20will%20display%20BUT%20WILL%20NOT%20SAVE%20THAT%20WAY.%20If%20I%20open%20the%20excel%20version%20copy%20the%20data%20and%20paste%20in%20note%20pad%20I%20get%20total%20gibberish%2C%20however%20copying%20and%20pasting%20from%20CSV%20into%20note%20pad%20shows%20the%20data%20with%20a%20comma%20delimiter%20but%20the%20volumes%20from%20column%20B%20all%20have%20quotes%20around%20them............%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20completely%20stumped%20on%20what%20to%20do.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20tried%20clearing%20all%20formulas%2C%20selecting%20the%20entire%20sheet%20and%20adding%20wrap%20text%20then%20removing%20it%2C%20selecting%20only%20the%20affected%20cells%20and%20removing%20the%20wrap%20text%20option%2C%20copying%20and%20pasting%20values%2C%20copying%20and%20pasting%20as%20text%2C%20and%20after%20each%20attempt%20I%20would%20save%20the%20document%2C%20re-open%20it%2C%20and%20sure%20enough%20those%203%20cells%20would%20be%20selected%20to%20wrap%20text.%20%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20727px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133924iD48FF9A75BB25F89%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22wrapissue.PNG%22%20title%3D%22wrapissue.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-880395%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-880428%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20removing%20wrap%20text%20from%203%20cells%2C%20not%20as%20easy%20as%20it%20sounds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880428%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416612%22%20target%3D%22_blank%22%3E%40penguinapplesauce%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20something%20like%20this%20and%20see%20if%20that%20resolves%20the%20issue.%3C%2FP%3E%3CP%3EIn%20a%20blank%20column%2C%20in%20Row2%2C%20place%20this%20formula...%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DTRIM(SUBSTITUTE(B2%2CCHAR(10)%2C%22%20%22))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20then%20copy%20it%20down%20to%20row4.%3C%2FP%3E%3CP%3EThen%20copy%20the%20three%20formula%20cells%2C%20select%20the%20cell%20B2%20and%20paste%20it%20as%20Values%20only%20and%20delete%20the%20formula%20cells.%20That%20should%20replace%20all%20the%20line%20breaks%20in%20B2%3AB4%20with%20space%20and%20trim%20them%20in%20the%20end%20to%20remove%20any%20leading%20or%20trailing%20spaces.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-880884%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20removing%20wrap%20text%20from%203%20cells%2C%20not%20as%20easy%20as%20it%20sounds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880884%22%20slang%3D%22en-US%22%3ETHANK%20YOU!%20Worked%20perfect!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-881085%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20removing%20wrap%20text%20from%203%20cells%2C%20not%20as%20easy%20as%20it%20sounds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-881085%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416612%22%20target%3D%22_blank%22%3E%40penguinapplesauce%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3C%2FLINGO-BODY%3E
penguinapplesauce
New Contributor

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 the column or remove the wrap text selection the value you will display BUT WILL NOT SAVE THAT WAY. If I open the excel version copy the data and paste in note pad I get total gibberish, however copying and pasting from CSV into note pad shows the data with a comma delimiter but the volumes from column B all have quotes around them............

I am completely stumped on what to do.

I have tried clearing all formulas, selecting the entire sheet and adding wrap text then removing it, selecting only the affected cells and removing the wrap text option, copying and pasting values, copying and pasting as text, and after each attempt I would save the document, re-open it, and sure enough those 3 cells would be selected to wrap text. wrapissue.PNG

3 Replies
Solution

@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.

THANK YOU! Worked perfect!

@penguinapplesauce 

You're welcome! Glad it worked as desired.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies