Expand my Community achievements bar.

Remove spaces in calculated strings

Avatar

Level 3
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.
9 Replies

Avatar

Community Advisor
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

Avatar

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

Avatar

Level 3
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.

Avatar

Level 4

@ByronNa 

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

Avatar

Community Advisor

 

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:

 

Doug_Den_Hoed__AtAppStore_0-1704329757670.png

 

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

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)

Avatar

Community Advisor

 

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

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

Avatar

Level 3

@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