View Full Version : EXCEL - find and repalce tabs in cells
friartuck
7th Sep 2018, 10:50
Does anyone know how to find and replace tabs embedded in cells with spaces?
Goggle suggest typing in ALT 0009 into the FIND box in FIND & REPLACE but it doesn't work on my machine - nothing appears in the FIND box............
MacBoero
7th Sep 2018, 14:41
I don't think you can even enter tabs into an Excel cell. If you have, how did you manage it, because I have so far been unsuccessful. So maybe where you think there are tabs there actually aren't any.
For example copy some text from Word that has tabs in it. When this is pasted into Excel, the tabs get replaced with spaces.
friartuck
8th Sep 2018, 08:50
Defo tabs......... you can go into the cell and delete them and the numbers all move together and then you can insert spaces or whatever or reinsert the tabs. The obvious answer is to do it one by one but it's a lot of work...
I suspect that I've inherited a spreadsheet where the cell input was a cut 'n paste job from another (mystical) spreadsheet .................
Defo tabs......... you can go into the cell and delete them and the numbers all move together and then you can insert spaces or whatever or reinsert the tabs.
How are you re-inserting the tabs? Pressing tab in an excel spreadsheet moves on to the next cell and pasting text that includes tabs will convert the tab to a space. As far as I am aware there is no paragraph level formatting in excel cells and tabs are not possible.
friartuck
8th Sep 2018, 10:30
If you google "excel cells replace tabs with spaces" you get quite a few answers but most come back to the ALT0009 find and replace - which obviously worked for some people
The real Jedi Masters have posts with all sorts of code and macros to do the same so it's clearly possible... but not simple
I've set a minion to do it manually - if I get time on Monday I might try and run down the originator of the spreadsheet and find where they got the cell data from (it's mainly text and looks like it may have come form another document -) I wonder if WORD may be the culprit.......
friartuck
8th Sep 2018, 15:42
Looks like it came from WORD - soooooooooo
1. Copy all the cells (or each cell at a time)
2. paste into a WORD document
3. in WORD open Replace and go to “More”
4. In “Find what” enter ^t
5. in “Replace with” enter two spaces or whatever
6. “Replace all”
7. Copy and repaste into the spreadsheet
Seems to work