Adding white space after specific characters

[jenner85]

Hi - I would like to add a space, but only if the string begins with specific characters "TD"

BUSINESS CASH BACK - 0017

TDCASH BACK - 0001

TDAE PLATINUM - 0010

TDREWARDS - 0014

BUSINESS TRAVEL - 0007

Would like it to be:

BUSINESS CASH BACK

TD CASH BACK

TD AE PLATINUM

TD REWARDS

BUSINESS TRAVEL

I know how to remove the numbers and punctuation, just not sure how to space the TD.

thanks! 

[jdunkerley79 · Accepted solution]

Two options both using a formula tool:

Plain method:

IIF(StartsWith([Field1], "TD") AND Substring([Field1],2,1) != " ",
    "TD " +Substring([Field1],2),
    [Field1])

or you can do REGEX:

REGEX_REPLACE([Field1],"^TD(\S)", "TD $1")

Quick sample attached

[Kenda · Accepted solution]

Hi @jenner85 

I would recommend utilizing the substring function in this case.

In a formula tool, I would use this expression:

iif(Substring([Field1],0,2)="TD", "TD " + Substring([Field1],2), [Field1])

Basically, this checks the first two characters of your string. If they're equal to "TD", then it adds a space and continues with the rest of the string. Otherwise, it keeps the original value.

Hope this helps!