Enhance display of positional encoding for debug purposes, perhaps using Excel one-character-per-cell

Question

I have the following problem to solve: I work with positional-encoded files. These files show characters at certain positions in the string. Each row is a record, and the meaning of a character in a row depends on its position. In order to debug and fix them I need to make sure about the cursor position on the current file line.

Most editors, including Notepad++, display the column position in the file. However, while helpful, I would like to make it simpler.

I want an editor to somway highlight character positions in my files.

Here is what I thought about: using Excel 2010 one-character-per-cell.

Ideally, if Excel allowed me to paste a text into a preformatted table with colours, borders and heading columns I would have solved my problem. But I must make sure Excel accepts a paste by inserting each character in a cell, going to the next row when a CRLF is found.

This is quite an XY problem and I hope I asked my question the best way

• Problem X: enhance readability of positional-encoded files
• Problem Y: given that Excel could be a good ally, how do I paste a whole file into one-cell-per-character with a single CTRL+V?

Example

Since real data is really complex to post in this question, a generic dataset can be expressed (in documentation) as follows:

• Each row makes a single record
• Character 0 is data type, alphanumeric
• Characters 1:8 are a last name
• Characters 9:15 are a first name
• Characters 16:30 are a phone number
• Characters 31:38 are a date of birth in yyyyMMdd format
• Filler characters are spaces
• Lines are terminated with CRLF

Example "valid" record (I'm typing it by hand)

0SMITH   JOHN    [phone]   19780101\r\n

Following the Excel example, I could display this data in coloured columns and easily edit a datasheet with colour/borders guides

Answer 1

Very much focusing on "one-character-per-cell" I'd suggest a template where ColumnA of Sheet2:5 is filled with Sheet1! B:B to E:E respectively. Then group so in each of Sheet2:5 B1 has =MID($A1,COLUMN()-1,1) and copy across and down as required. Sheet1 being reserved for your source data:

Answer 2

The following formula seems to work fine when displaying the dataset

Localized

=STRINGA.ESTRAI(INDIRETTO(CONCATENA("PASTEME!A";RIF.RIGA()));RIF.COLONNA();1)

Hand-Internationalized (please correct function names in English Excel)

=MID(INDIRECT(CONCATENATE("PASTEME!A",ROW())),COLUMN(),1)

Now I just have to copy it to the whole sheet and add formatting/bordering/colouring (thousands of records in each dataset)

The problem with the formula approach is that I can't easily edit data on the dataset, but at least I have a clear idea of where to search for editing and where a bug could be in the dataset (or simply "immediately understanding the data")

Answer 3

The easiest way to do this is to simply use a preformatted Excel worksheet (template) and use the Data>From Text>Fixed Width option. This will allow you to bring any/all characters in their own cell, and you can start your range at any spreadsheet cell you like.

If this is going to be a repeated task, I'd use VBA to automate the process a bit, perhaps using a dialog box to ask for a file and a point to being the insertion, but doing all the rest automatically.

As for your Y, I don't think any of the Paste options, especially CTRLV will do what you want.