I have Excel 2010 on Windows 7. I have a huge spreadsheet with 55,000 lines so I'm looking to remove all hidden tabs in each spreadsheet in one step. So the clean() function does not work for me.



I've tried the Search and Replace dialog buy holding ALT and typing 0009 to enter a tab, and replace it with blank, but that doesn't work. I get no error, and the tabs are not removed. I don't even get another dialog asking me to find the next occurrence to replace.

This spreadsheet is from a customer and no, they can't fix it, they just don't know how. And no, we can't upgrade office.

Anyone have any ideas? I can type in VBA and run some code on every cell but I'm not a VBA programmer so I wouldn't know where to start. After we remove the tabs I will export the spreadsheet to a tab-delimited file. Reprogramming the program to use this tab-delimited file would take at least a week due to my busy schedule so that is not an option here.

Hi and welcome
can you post a sample sheet with some of the cells concerned? ( click Go Advanced - Manage attachments)

EDIT if you run = CODE(SOMECELL) what does it return? ( somecell is a cell reference)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

This addin might help to identify the problem

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

- The Cellview addin is what I already have, and normally works to show hidden characters, but that does not help me delete these characters in one step.
- It will take me a bit to delete thousands of lines to show only the weird lines.
- Doing =code(c35583) gives me 67, and on another cell gives me 70.
- Is there an easy way to specify a range or rows to delete without taking 5 minutes to select them? Or delete all rows not shown via a filter? I'm trying to smallify and sanitize this spreadsheet a bit for privacy.

I've attached a file test1b.xlsx. In col C in normal view you cannot see the embedded tabs. But if you use Cellview, the tabs are at the end of col C. This really messes things up when I export the spreadsheet to a tab-delimited file.

Keep in mind this is a very small test. The real spreadsheet has 55,000 rows.

Attached Files

test1b.xlsx (9.5 KB, 8 views)

What you mean by "Tabs"?

ASCII code 70 is for "F" and 67 for "C". What have they common with tab?

Aren't you set Wrap Text on from CellFormat.Alignment tab?

There are hidden tab characters in col C along with other letter characters.

Nothing. Someone asked me to run that formula "=code()" on the cell in col C.

I don't understand. Can you rephrase?

When you select a cell (range), right-click on it, and select 'Format Cells', a Format Cells wizard with several tabs is opened. When you activate the second tab, dubbed as 'Alignment', then there you can check a format option 'Wrap Text', which allows cell entries on different rows (it wraps text automatically to several rows, when it is too wide for cell, and also you can enter a "Hard Return" to force a new row. Maybe there are other options too, but I haven't looked for them. Anyway, this "Hard Return" must have some code in cell too.

Have you tried =CLEAN(C1)?

I'd select the cells concerned and run a macro