•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Trying to remove invisible characters (go to special -> blanks not finding all blank cells)
Our take
If you’re struggling to remove invisible characters from your Excel data, you’re not alone. After copying a large dataset, you might find unexpected blank lines disrupting the flow. Even when using "Go To Special" to locate blanks, Excel may not recognize them. This issue could stem from how data was extracted, particularly if the original file format differs. With tools like CLEAN and TRIM yielding #VALUE errors, it’s crucial to identify the root cause.
Hi everyone, I’m at my wits end with this one. I’ve copy and pasted data a large chunk of data from one excel sheet to another that has a blank line or two between meaningful data. I want to remove all those blank cells, but when I use go to special -> blanks it says there isn’t any. I’ve tried using CLEAN, tried using TRIM, tried using both, tried using CODE to detect what character is in the cell, it comes back as #VALUE.
The way the data was pulled was with the formula IFERROR(TEXTBEFORE(A1, “ - “,1)””). It isn’t treating “” as blank for whatever reason. Maybe an extra important detail: the original file is a .xls; new book is .xlsx. Using Office 365.
Any help is appreciated!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Cells not recognizing data as numbers, tried every trick I found onlineSo I copy and pasted some data into excel. I copied row by row from a pdf into an excel because the converting feature wasn't working well. Now, none of the data is being recognized as numbers except 0's (on left side of cell, can't be used in equations, there isn't an error on the cells but error when trying to use them as numbers). I looked into it and tried all the tricks I could find and the only thing that works is going to individual cells, retyping the numbers and hitting enter but I would love to find an easier way to convert all this data. Here's what I've tried: Hitting enter on the cells Typing and copying 1 and then special paste > multiplying by one Removing space with text to columns Using the value command I'm not sure if maybe I just don't understand what the issue is, but I need to use the data for a coding assignment and right now my coding software doesn't want the csv because the cells are recognized by numbers. Does anyone know the best way to fix this? submitted by /u/Lonely_Shower4385 [link] [comments]
- How to remove blank space from pivot tableI'm doing an assignment where we have to put our data into a table but I cant seem to get rid of the blank column, is there a function to get rid of it? I'm new to excel and have already tried some methods that did not work so I'm unsure if I might've done them the wrong way? Also I think I'm using the most recent version of excel https://preview.redd.it/45higvez8wqg1.png?width=610&format=png&auto=webp&s=12273a9ed89b8151f3d1d4c8368a78829e6b73fa submitted by /u/Lone_rerednow [link] [comments]
- Removing every instance of a text value I dont wantHi, I have a report ive been working that's usually takes in about 400 entries in 7 different columns that are generated by an external system. When this system exports everything into an excel file it includes the the headers on every sheet and sometimes multiple on the same sheet. This makes roughly 30 ish of the 400 entries just useless headers. I have been removing them manually with find and replace but really want to find a formula that removes them for me submitted by /u/Intelligent_Let7166 [link] [comments]
- Excel in page layout - How to remove pages without any content?My spreadsheet behaves somewhat strangely. The pages without content aren't all greyed out. At the same time the cell grid on those pages is shown cropped. Printing the sheet results in two pages. One with all my content and one that's blank. How do I remove this blank page? Selecting the columns AD to AS and deleting them doesn't solve the issue. On the contrary, it just makes it worse. Now there would be five blank pages when printed. https://preview.redd.it/toybdete030h1.png?width=2473&format=png&auto=webp&s=ec611af7dccf21ccf8806340c93e2efd86392014 Win 11 with Office 2024 submitted by /u/Wild-Sir8868 [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#Excel compatibility#financial modeling with spreadsheets#Excel alternatives#rows.com#natural language processing for spreadsheets#AI formula generation techniques#large dataset processing#no-code spreadsheet solutions#formula generator