Add whitespace and change beginning of words to uppercase

[beatrizmguerreiro]

Hello, 

i have the situation below:

DATAC  LEASING TOOL AND JOIN TOOL & Summarize

And I need the output to be:

Data Cleasing Tool and Join Tool & Summarize

Most of the word will be uppercase, in the beginning and also eliminate that extra whitespace between the "C" and "L". I have tried the Data Cleansing Tool and Formulas, but no success. 

Can somebody help me to fix this? 

[messi007 · Accepted solution]

@beatrizmguerreiro,

Please see below:

Attached the workflow,

If this solves your issue please mark the answer as correct, it will help others!

Regards,

[beatrizmguerreiro]

It worked. 

However I still have that extra whitespace. 

I added a data cleasing tool and it clean all trialing whitespaces. However, after using a filter tool, the field shifts. It adds a whitespace and I can't seem to understand the reason.

[AngelosPachis]

Hi @beatrizmguerreiro ,

From what I understand from your question, it's not just a matter of removing the whitespace between "C" and "LEANSING" but also re-assign it to the previous word, correct?

If you totally remove the whitespace, then DATAC LEANSING would return DATACLEANSING, 

but 

you are looking to get DATA CLEANSING.

Is that right?

[Greg_Murray · Accepted solution]

Hi @beatrizmguerreiro, 

This expression in a formula tool should do the trick. Not this is not dynamic and and will only work for strings that match the example you gave. The regex_replace bit replaces 2 or more spaces with nothing and the replace part handles the space error between 'data' and 'cleansing'.

Replace(REGEX_Replace([YOUR_FIELD_HERE], '\s{2,}', ''),'DATAC','DATA C')

Hope that helps,

Greg 

[beatrizmguerreiro]

Yes, correct