Convert replace formula to Alteryx

[Polina1234]

Hi there,

I need to do replace, excel formula is =REPLACE(A1,2,1,"_")

Can anyone help me to convert it to Alteryx?

thank you

[mceleavey]

Hi @Polina1234 ,

the syntax is replace([field],<String>,<replacement>)

If you click on the function button in the formula tool you will be given a list of all the functions available and you can select it from there. It will then give you the syntax.

Hope this helps.

M.

[KarolinaRoza · Accepted solution]

hi,

Please try:

left([Field1],1)+"_"+Substring([Field1],2)

Karolina

[AngelosPachis]

Hi @Polina1234 ,

Using the replace excel formula you are looking to replace the substring that starts at position number 2 of your string (the second character that appears in your text) and has a length of 1.

In Alteryx, one way that I can think of to do that (and hopefully I'm not overcomplicating it) is to look for the substring that starts at position 2 and has a length of 1 (note that in alteryx, you start counting characters from 0 and not 1). That you can do with a substring formula.

Then you want to replace the first occurrence of this substring with an underscore, so this is where you want to use the Replace function; since you only want to replace the first occurrence of this substring and not every occurrence, you can use a ReplaceFirst formula instead.

Not sure if this is the most robust way though so I look forward to see what other people have to suggest.

Cheers,

Angelos

[KarolinaRoza]

Hi @AngelosPachis ,

I am not sure about ReplaceFirst(), in case there is values like "aabcd" it will probably not work..

Karolina

[AngelosPachis]

You 're right @KarolinaRoza 👍

In that case it wouldn't work, thanks for flagging it