Resolving an Excel Non-Breaking Space using SubField()

stevegpage1 — Fri, 02 Apr 2021 08:04:16 GMT

I am working with a large set of Excel files that I am importing into Qlik Sense. One of the fields that I need to import comes from a long text string. The string had a set of document numbers embedded in the string. Using the Index() and Mid() functions, I was able to locate and extract the document number. Since the document number could vary in length, I attempted to use the SubField() function to pull the document number out, by delimiting on the <space> that followed the document number.

Example: SubField(Mid(Index("Account Description", 'N01'), 15), ' ', 1) AS DocumentNo

In most cases, this worked perfectly. However, in reviewing the results, I would get things like:

N011234XYZ12 01 - which still includes a space in the document number. After reviewing the source data, I discovered that in some cases, the data (which had initially been stored in SharePoint) included a "Non-Breaking Space" (nbsp) (https://en.wikipedia.org/wiki/Non-breaking_space) . The excel files where this comes from cannot be edited, so the modification had to be done in the Qlik Sense script editor.

After researching this issue, my solution was to add an additional SubField() function, by including the "invisible" nbsp. To do so, my resulting solution looked like:

SubField(SubField(Mid(Index("Account Description", 'N01'), 15), ' ', 1), ' ', 1) AS DocumentNo

Where in the second ' ' I typed <ALT+255> (Hold the ALT key down, and type 255). The result returned just the document number, without the extra 'space' and characters - N011234XYZ12.

Hopefully, this will save someone some time if you come across this problem!

Re: Resolving an Excel Non-Breaking Space using SubField()

stevegpage1 — Thu, 15 Aug 2019 16:03:50 GMT

Solution:

Add a SubField() function, by including the "invisible" nbsp.

Example: SubField(SubField(Mid(Index("Account Description", 'N01'), 15), ' ', 1), ' ', 1) AS DocumentNo

Where in the second ' ' I typed <ALT+255> (Hold the ALT key down, and type 255).

topic Resolving an Excel Non-Breaking Space using SubField() in App Development

Resolving an Excel Non-Breaking Space using SubField()

Re: Resolving an Excel Non-Breaking Space using SubField()