This article is for customers having data best displayed as a multiple lines. This could be a bullet list or just a simple set of multiple lines such as shown below.
Here we show several techniques to improve your workflow and final presentation.
Background
Separate lines are sometimes used within a cell in Excel, often when Excel is being used for reporting. At the end of each line a special character, the line-feed character is inserted to create a separate ‘row’ in the cell. This is done by pressing Alt+Enter at the end of the ‘row’.
This special character is more or less, invisible. Importantly, these don’t translate into separate lines for a browser. The data in your spreadsheet must be transformed to contain HTML markup. In HTML the "<br />" markup is treated as a single line break.
These line-feed characters can also be a bit tedious to deal with especially when you're copy/pasting data from another source like Word or another system as they are taken as data for adjacent cells when pasted into the cell because of the line-feed. They must be pasted into the formula bar to which requires visiting each cell at a time.
Text having line-feeds pasted into Excel cell - filling adjacent cells.
Text having line-feeds pasted into Excel formula bar.
These special characters can also become unwieldy where there are a significant number of these ‘rows’ in a cell. In the above images we've expanded the formula bar from the typical one line display - imagine if there we dozens of 'rows'.
Applying the HTML Break Tag
To transform these line-feed characters into the required HTML a simple way is to use the Excel Find and Replace function. These are the essential steps to accomplish this:
Highlight the column containing the content to be transformed
Use Ctrl+H to bring up the Find and Replace dialog
Use the Ctrl+j sequence in the “Find what” box. This inserts the line-feed character
In the Replace with box use “<br />”, the HTML break tag
Press the Replace All button
Note these are Windows shortcuts. Consult a Mac resource for their equivalents.
The <br /> characters will replace the line-feed characters.
This replacement can also be accomplished with a formula as shown here.
This is the formula/function you see in the cell:
=SUBSTITUTE(A1,CHAR(10),"<br />")
Replace the column “A1” with the column you’re using, then use the Fill > Down function of Excel to apply it to the target rows. The “CHAR(10) is an expression of the line-feed character.
Alternatives to the Line-Feed Character
Another way to handle the difficulties of using the line-feed character is to instead use a select character that doesn't appear in the data rather than the line-feed character. Rather than use the line-feed character itself you would choose a character to represent the line-feed and for this article we will use the "^" character since it is unusual and doesn’t often appear in text.
Also here are two HTML codes that you might consider using at the beginning of your text lines: — • producing the — and • respectively.
You could also use another uncommon character to substitute for these characters e.g. “~” for • and use character replacement as has been demonstrated.
A representative display using the transformation above.
Defining the Field/Column in MetaLocator
Within MetaLocator you must first make sure the field is sized correctly and of the correct type. The field must be (re)defined as HTML. Often the original field definition is a length of 255 and text. The length of 255 should be adjusted if that will not be of sufficient size for your content. Often it's just best to delete the field and redefine it as HTML/Long.