Remove Special Characters

[prees959]

Hi,

I am using this formula in Excel to remove a leading white space :

TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32)))

Is there a similar function to do this in Qlikview.  I have tried a simple LTRIM but this doesn't seem to work.

Many thanks.

Phil

[YoussefBelloum]

Hi Phil,

Can you try some of these:

• Replace(Field,' ','')
• TRIM(REPLACE(Field,' ',''))
• Purgechar(Field,' ')

You can try to combine these functions also.

Good Luck

[its_anandrjs]

You can use

=Trim( FieldName )

[sunny_talwar]

Try this

PurgeChar(A2, Chr(160)&Chr(32))

[ashwinishinde]

Try this:

PurgeChar(A2, Chr(160)&Chr(32))

thanks,

ashwini

[Peter_Cammaert]

How about this:

Trim( Replace( A2, Chr(160), ' ' ))

Check the examples on the pages that each function links to.

[Peter_Cammaert]

An even more complete reproduction of the Excel function Trim uses MapSubString to replace series of spaces. But this is only usable in your Load Script...

MapMultiSpace:

MAPPING

LOAD Repeat( ' ', RowNo()+1) AS Index, ' ' AS Replacement

AUTOGENERATE 9;

LOAD

  :

  Trim( MapSubString‌( 'MapMultiSpace', Replace( A2, Chr(160), ' ' ))) AS CorrectedA2,

  :

FROM ...;

Best,

Peter

[joydipp1988]

Hi Sunny,

What are the characters defined by Chr(160) and Chr(32) ?

Is that spaces or any special characters?

Any link you to get more info on this?

Thanks,

Joy

[nsetty]

chr(32) is space.

chr(160) is non-breaking space.

[Peter_Cammaert]

If you want to find out about other characters represented by numbers between 0 and 65535, check any on-line Unicode table. Like this one:

https://unicode-table.com/en/