OS and Software → Microsoft > Find and Replace TAB character in cells

Find and Replace TAB character in cells

The word TAB being so popular makes for a tough google search so not finding joy. I have a report that has the TAB character in the middle of a string that I need to remove as part of the string.

Using special chars like ^t or ^9 don't cut it.

Oh, it is Excel 2003 if that matters.

Press Ctrl-F to open the Find dialog. Hold down the Alt key, type 0009 in the Find what field using the numeric keypad, and release the Alt key. Click on Find Next.

If you're using a laptop and don't have a numeric keypad, press Fn and NumLk key before you do this.

Or you could use Excel's CLEAN function to remove all nonprintable characters from a string.

I did try [alt]0009 but that did not work. Maybe that only works in versions of Excel newer than 2003. I can use a formula but that doesn't replace in the original cell but rather populates the change to an adjacent cell. Does CLEAN work on the cell in place?

I may have to delve into VBA macros...
--
Strange as it seems, no amount of learning can cure stupidity, and formal education positively fortifies it. -- Stephen Vizinczey

CLEAN removes the first 32 nonprintable ASCII characters, but it doesn't work on the cell in place.

What happens if you press Ctrl+H, type CHAR(9) in the Find what field, and then click on Find All?

If that finds the TAB characters, enter whatever you like in the Replace with field surrounded by quotes (for example, "ZZ" or " " for a space or "" for nothing) and then click on Replace All.

CHAR(9) is taken literally so that won't work. Short of a macro, I think there is no other way, at least not through the Find and Replace UI. Thanks for trying though.

That said, in this particular case the TAB char is preceded by a consistent and unique char sequence that I can use in my find and I can substitute the TAB with the single char wildcard ? so I have a workaround.
--
Strange as it seems, no amount of learning can cure stupidity, and formal education positively fortifies it. -- Stephen Vizinczey

I think your Excel may be newer. Mine is 2003.

I am sure it is Char(9) cuz the formula:
=Substitute(A3,char(9),char(32))
changes it to a space albeit not in the same cell.

Also, I copy/pasted the cell contents into notepad, saved it as a txt file, and then iterated through each Char with a script which returned asc(9) for it.
--
Strange as it seems, no amount of learning can cure stupidity, and formal education positively fortifies it. -- Stephen Vizinczey

Both of these methods work in Excel 2003 and Excel 2007 for me.

Is the TAB character visible? If so, have you tried copying and pasting it into the Find what field using Ctrl-V. Sometimes that won't work, but this does work, for me, in both versions of Excel.

If you have a sample file with just a couple of cells with the TAB character, I'd be happy to take a look at it. No promises though...

In the cell it shows as a rectangle and yes, I did try copying it with Ctrl-C and pasting it into the find field. Copy/pasting even the entire cell contents fails to find it.

If it's not the version, maybe we are on different SP or patch levels, or maybe the OS version is a factor.
--
Strange as it seems, no amount of learning can cure stupidity, and formal education positively fortifies it. -- Stephen Vizinczey

Thanks for the tip.