Add-Innovation Home

About Add-Innovation

Contact Us

Resetting Excel's Delimiters.

What are "Excel's Delimiters" ?

Working with Excel allows us to use data from all kinds of sources. Several of these sources may come from other workbooks, or other Microsoft applications. In these cases, the data is often in a format which can be readily opened by MS-Excel (e.g. a data table in MS-Access, or a table in a word document which may be cut and pasted into our new document easily).

Other sources may take data from the application they are created in, and "export" the data in a for5mat which can be easily understood by other applications. This is fairly commonplace, and indeed Excel supports saving worksheets as .txt (text format data with columns separated by tab characters), or .csv (again text, with columns separated by commas). Other common formats include space delimited, and fixed width data where the data is always in the same position, and another format is the simple cut and paste from a screen full of text (e.g. on a mainframe screen) straight into Excel. Exporting data usually means that all of the formatting of the data is lost, but the data (the important part !) remains.

Importing the data back into Excel is quite easy. When we click on the Open dialogue in Excel, we can click on the "Files of Type" box, to reveal an additional set of options as shown below.

File open dialogue showing how to change the type of file being opened.

Here we can see the last option, "text files", which would allow us to import straight text, with some kind of delimiter which would tell Excel where the column boundaries were. If we selected such a file, the text import wizard would open - a 3 step process involving specifying how the data should be broken into columns, and how the data is defined. The first process window is shown below ;

Text import wizard box # 1, showing selection of delimiters rather than fixed width.

In this case, I know the data is delimited by a tab separator so I'll stick with the "delimited" radio button, and click next. This leads on to display ;

Text Import Wizard step 2 dialogue box, showing selection of tabs and commas as the delimiter.

Notice that Excel ticks both boxes comma and tab, allowing me to use either. If I just click finish, my text now looks like ;

Text split across several columns - just as I wanted !

Which is exactly what I want.

Another option is cutting and pasting straight from a text display in another application. The following example shows how this may appear in a text editor like notepad ;

Store, Sales, Orders,Items
02007,£1234.56,987,987654
02010,£2345.67,77.22,5555
02896,£576245.26,12345657,77888899

and after the cut and paste ...

Text pasted into rows, but all in one column.

I can select Data, Text to columns to open up the text to columns wizard, and specify that this data is comma delimited, and split it down that way.

The trouble is ...

... like an elephant, Excel doesn't forget. If we import a .csv file, or choose text to columns and then select comma delimited, Excel expects all data to look that way, and if we copy a text value of say 1,234,567 and paste it into Excel, it will merrily paste it into three cells, and won't even ask us if we want to replace the destination cells (though it would allow an undo operation).

My (and a lot of people's) bugbear about this, is that it's more likely that we'd want to just paste as text, and then choose to delimit the data ourselves, rather than set this as the default. Unfortunately Excel doesn't think the same way.

So the Tesco Toolbar adds an option to reset delimiters, and that's what it does. In effect, it tries to delimit cell A1 using no delimiters whatsoever. There's no chance that the data will be split across other cells, because no delimiter is presented, and the cell is reset to whatever it was (including blank) before the operations was carried out, so it's perfectly safe. The end result is that Excel doesn't register any delimiters at all, and we can cut and paste as much as we want without any text to columns conversion being carried out.