Issue with .xls having a ' (single quote in front of all data)

[MJohnson10]

Having an issue when doing an output to .xls



Each column has that single quote ' and it is hidden as you see it can only be seen in the highlighted area but not in the data cell. and i need the file format to be .xls but this hidden character has been failing to upload due to this character. any help would be greatly appreciated!

I am at a loss and any help would be appreciated!

- Michael Johnson

[JoeS]

Sorry, not quite sure what you are looking to achieve. 

Is this that your input data has these or the data you output from Alteryx?

[MJohnson10]

output from alteryx to an .xls format

[JoeS]

Alteryx will output any string fields with this value on the front to tell Excel that it's a string value contained in the cell.

What's the issue that it then causes?

[MJohnson10]

Hello,

the file is to be uploaded into a shipping terminal.

[JoeS]

Ah, and that then rejects them for having the single quotes?

Is there no chance they could accept the XLSX instead? (it has been around now for 12 years!)

If so, I think the only work around I'd know would be to use VBA\VBS. Alteryx uses Microsoft drivers (Access Database Engine) to create an XLS file, so it's something that is handled by that I believe.

[JoeS]

There is a VBA macro in here that should help.

Sub QuoteKleaner()
    Dim s As String, r As Range
    Dim rBig As Range

    Set rBig = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeConstants)
    For Each r In rBig
        If r.PrefixCharacter = "'" Then
            r.Value = "//" & r.Value
        End If
    Next r
End Sub

[estherb47 · Accepted solution]

Hi @MJohnson10 

If at all possible, save from Alteryx as an .xlsx format, and then try using Excel to save as an .xls.

Easiest solution that has worked in the past for me.

Cheers!

Esther

[MJohnson10]

Yes, i currently do that and i can confirm it works. 

But i do want to remove that manual step 🙂

[MJohnson10]

interesting, i have not used VBA before in alteryx