1

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    +13652145896   19780101\r\n

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

Community
  • 1
usr-local-ΕΨΗΕΛΩΝ
  • 4,592
  • 16
  • 67
  • 103
  • This could be also suitable for *stackoverflow* – usr-local-ΕΨΗΕΛΩΝ Jun 05 '13 at 08:28
  • The example helps, thank you for adding it. This looks like a standard fixed width text file, so it should be pretty easy to work with. When you talk about editing the file, are you doing data cleaning to get it ready to import into a database/other program? If so, do you just need data validation for each column/character? – dav Jun 05 '13 at 14:29
  • No, I validate "by hand" and I'm going to copy/paste cells into a text file to be imported by an application – usr-local-ΕΨΗΕΛΩΝ Jun 05 '13 at 14:33
  • Also, is the data validation just for data type (e.g. alpha v. numeric) or for the actual data (e.g. Last name is a real name, not just random characters)? – dav Jun 05 '13 at 14:41
  • 1
    The last one. The real specs are far more complex and I require certain fields to match given codes and when a specific code is present some fields must be present while some others not. Complete validation is already implemented in importing software, but my need is to ease fixing: the most common error is a wrong number of spaces – usr-local-ΕΨΗΕΛΩΝ Jun 05 '13 at 14:44

3 Answers3

1

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")

pnuts
  • 6,072
  • 3
  • 28
  • 41
usr-local-ΕΨΗΕΛΩΝ
  • 4,592
  • 16
  • 67
  • 103
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:

SU603915 example

pnuts
  • 6,072
  • 3
  • 28
  • 41
  • 1
    Anyway I found out that splitting the record in substrings rather than in "one-cell-per-character" enhances readability more than achieved right now. Upvoted because answers the question – usr-local-ΕΨΗΕΛΩΝ Jun 05 '13 at 13:34
0

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.

dav
  • 10,021
  • 5
  • 30
  • 52
  • I think you partially answered my Y part: if VBA can be used, it *can be used* to split the string that is pasted into the spreadsheet, IMHO. But I don't know VBA and if it supports intercepting the paste event, but could be a good starting point. – usr-local-ΕΨΗΕΛΩΝ Jun 05 '13 at 12:13
  • Also, I was seeking for a few formulas that could help me split a string pasted in another cell/sheet – usr-local-ΕΨΗΕΛΩΝ Jun 05 '13 at 12:15
  • 1) I think we're talking two sides of the same issue-I'm pretty sure VBA could use Paste as part of it's process, but it wouldn't be Paste, it would be VBA using Paste-a minor, but I think imporatant distinction. 2) There may be some formulaic methods for addressing text pasted elsewhere in the spreadsheet. Could you provide a small sample of your data? If you're open to a VBA solution, you should add the tag, you'll probably bring in some extra eyes on your question. – dav Jun 05 '13 at 12:27
  • I would use VBA only as last resort. Tag added – usr-local-ΕΨΗΕΛΩΝ Jun 05 '13 at 12:34