Resolving an Excel Non-Breaking Space using SubField()

[stevegpage1]

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!

[stevegpage1 · Accepted solution]

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