Paragraph and Line Breaks
I. The problem is that the Manual Line Break cannot be COPY & PASTE in Excel, as it interprets it as a space (“ “ – the blank area between the two quotation marks is actually a SPACE!), with the result that the above Formula is counting the position of the first space in the indicated Cell.
II. I found this LINK but it doesn’t work as the Manual Line Break in the exported Formulation data is represented by a Mars Symbol, which, according to the Merriam-Webster font(s) (MW_FONT) is represented as i0234. This is different than the “carriage return operator” or “return“, which looks like this: ¶.
III. However FileMaker uses the following combination in their Calculations & Formulae to represent a carriage return (paragraph): \¶ (backslash followed by a carriage return operator) See: [i] for more explanations.
IV. In Excel you can enter a Manual Line Break by holding down the Alt Key and typing “010” (minus quotes) – this only works in the Find/Replace Dialogue Box in Excel, not with direct entry (if you try to do this in FileMaker, an error message will pop up: “The provided find criteria is not valid. Enter a valid request before proceeding”). You can also enter a Manual Line Break, using the following Formula: “CHAR (10)” (minus quotes).
V. Perhaps it is really the LineFeed (LF) Character (or Newline Character), which in UTF8 is “0A”. By the way, 09 =tab character, 0D = Carriage return (CR) character (See more on this HERE).
VI. The problem is that the character that FileMaker uses to create a new line is invisible; as in “
a. ” (note the new line, which happened when I pasted the Mars Symbol from FileMaker – NOT because the text on the previous line reached the end of the paper width…!). When I pasted it in the Find What Field of the Find/Replace Dialogue Box in FileMaker, it correctly identified (aka Found) the character – i.e. at the end of a string of text, before the new line - when I pressed the Find Next Button it correctly found it.
1. I found a VB Script to carry out the task at hand, but before it can work properly I first need to clean the data:
a. Replace all Comma’s (impossible), Semicolons (impossible) etc., so they don’t interfere with the Formulae
b. Clean the text from Spellcheck errors as far as the hand reaches…
