Remove spaces in calculated strings

[ByronNa]

The REPLACE() function seems to behave normally. Until you try to replace a " "(space) with a ""(no space). It doesn't like the no space thing. How do I remove all the spaces from a string? I can put in about any other character but not "no space". Byron Nash INSP, LLC.

[Doug_Den_Hoed__AtAppStore]

Huh: confirmed, Byron. I've never noticed that. I'd suggest you report it as a bug, but suspect there might be a technical/security reason preventing it (although I can't think of one, myself). Regards, Doug Doug Den Hoed - AtAppStore

[ByronNa]

Thanks, I submitted a ticket. Byron Nash INSP, LLC.

[ByronNa]

It appears to be a limitation of the syntax according to support. Does anyone have any workarounds for this sort of thing? Byron Nash INSP, LLC.

[RobertDy]

@ByronNa 

Still appears to be happening. I can't think of a workaround. Did you find one??

[Doug_Den_Hoed__AtAppStore]

Hi @ByronNa and @RobertDy,

Thanks for re-raising this challenge. I decided to solve it.

Unfortunately, the bug preventing the REPLACE function from substituting a " " with "" has not yet been fixed:

REPLACE({name}," ","")

However...

After a transcendental meander through my Save Until Needed Vault, I am pleased to share the following workaround, as shown below:

Here is the textmode for each column:

displayname=Replace Spaces With ~
textmode=true
valueexpression=REPLACE({name},' ','~')
valueformat=HTML

displayname=Replace Spaces with New Line
textmode=true
valueexpression=REPLACE({name}," ","\\u000A")
valueformat=HTML

and last but not least, the One You're After:

displayname=Replace Spaces with Null Character
textmode=true
valueexpression=REPLACE({name}," ","\\u0000")
valueformat=HTML

Note, though, that (as I mentioned in this related post)...because these formulas leverage Unicode control characters there is a GOTCHA: "...remember to always re-supply the extra leading \ symbols that are sacrificed whenever such calculations are edited via the front end (vs pushing such calcs in via the API)."

That aside: one more down, zillions to go.

Have fun.

Regards,

Doug

[RobertDy]

Thanks for looking into this @Doug_Den_Hoed__AtAppStore 

So, when I tried this as a calculated field on a custom form, this is how I had to implement it to work:

REPLACE("test123","123","\u0000")   =>  "test"

(Only a single \ symbol)

Really useful to have these two codes. Thank you.

Hopefully Workfront will resolve this bug.... (I won't be holding my breath though)

[Doug_Den_Hoed__AtAppStore]

My pleasure @RobertDy,

That is encouraging that the custom form builder you used is "smart enough" to not need the double \\ prefix that the textmode editor requires (since the latter "swallows" the leading \ upon save): perhaps the dev team can use the former to expedite fixing the latter (cc @jon_chen to relay accordingly, please).

Regards,

Doug

[AndyKent]

Hey Doug

Similar issue, I have the following calculation to create a string but I need to remove the character spaces in {name}

CONCAT(

{project}.{DE:Fiscal Year for DAM},"_",

{project}.{DE:Product},"_",

{project}.{DE:IC - Market},"_",

{project}.{DE:Segment},"_",

{DE:IC - Asset Type},"_",

{DE:Funnel},"_",

REPLACE({name}," ","\u0000"), "_",

{project}.{DE:Prospective customer industry/vertical},"_",

{DE:IC - Asset Size},"_",

{referenceNumber})

Your \0000 works in the custom form but throws an SQL error when a value is updated in the report.

Essentially I want to get this:

23_SX3_DE_SMA_10"_MOFU_SocialPostCopyOption3_AUTO_Vide_9x16_2649318

from this:

23_SX3_DE_SMA_10"_MOFU_Social Post Copy Option 3_AUTO_Vide_9x16_2649318

Wondered if you had any suggestions?

Andy

[AndyKent]

@Doug_Den_Hoed__AtAppStore @RobertDy 

CONCAT(

{project}.{DE:Fiscal Year for DAM},"_",

{project}.{DE:Product},"_",

{project}.{DE:IC - Market},"_",

{project}.{DE:Segment},"_",

{DE:IC - Asset Type},"_",

{DE:Funnel},"_",

REPLACE({name}," ", "\u200B"), "_",

{project}.{DE:Prospective customer industry/vertical},"_",

{DE:IC - Asset Size},"_",

{referenceNumber})

23_SX3_DE_SMA_Blog_MOFU_This​Is​The​Name​Of​An​Asset_AUTO_Vide_16x9_2649250

Zero width unicode here - ​ - Zero Width Space, Unicode Number: U+200B Symbol Meaning ✂ Copy & Paste (◕‿◕) SYMBL