building strings

[Anonymous]

Hello Community,

i'm trying to build an email from a colmun with full names. Right now i'm using substitute to replace all blank spaces with a dot and then i add the domain name. But since there are people working here with three names i need to add a underscore before the third name, like this: john.donothan_wayne@outlook.com. Is there any way to iterate through the string or any other ways yoou can suggest?

Thank you!

[Anonymous]

Alternatively I'd build a custom column in the query editor that splits on the spaces in the names, split on space then concatenate them together with 2 custom columns will probably be the easiest way to do it, might not be the most efficient though.

[Anonymous · Accepted solution]

You can use a third argument in SUBSTITUE to dictate the occurance that you want to substitue =SUBSTITUTE([name]," ","_",2)

I'd use a switch that checks the number of time a space occurs  LEN([name])-LEN(SUBSTITUTE([name]," ","")) then have a result for 1 space or 2.

[Anonymous]

Oh wow that was more easy then i thought, i used the Substitute solution with the second argument and that fullfills my needs. Thanks!