Hi,

can you help with a logic. I have data as below

0003-abcd-def
0004-efcg-hedg
0005-htej-khlu

and i want to change the data as below. In the place of first zero, i need to have "D"

D003-abcd-def
D004-efcg-hedg
D005-htej-khlu
#ApptioforAll

You could use the Substitute() function.  In your case is would be something like:

=Substitute(MyField,"000","D00")

But this would assume that your data always starts with 3 zeros.  If you had a record that started with "0010", this wouldn't work.  And this also assumes you don't have 3 zeros in succession anywhere else in the string.  If you had "0003-a000-def", this would work to replace the first zero in both instances so you'd end up with "D003-aD00-def".

I'm assuming this is 3 different records, correct?  If so, you could evaluate the length of the string, subtract 1 from it, and then take the resulting number of characters and append D to the front of it.  This would utilize the Len() and Right() functions.  That would look something like this:

="D"&Right(MyField,Len(MyField)-1)

Very elegant, @James Funk!​

Thank You!
Original Message:
Sent: 09-29-2022 06:47
From: James Funk
Subject: Logic

You could use the Substitute() function.  In your case is would be something like:

=Substitute(MyField,"000","D00")

But this would assume that your data always starts with 3 zeros.  If you had a record that started with "0010", this wouldn't work.  And this also assumes you don't have 3 zeros in succession anywhere else in the string.  If you had "0003-a000-def", this would work to replace the first zero in both instances so you'd end up with "D003-aD00-def".

I'm assuming this is 3 different records, correct?  If so, you could evaluate the length of the string, subtract 1 from it, and then take the resulting number of characters and append D to the front of it.  This would utilize the Len() and Right() functions.  That would look something like this:

="D"&Right(MyField,Len(MyField)-1)

I have always like the ReplaceRegex formula although it can be performance heavy, so do use with some caution. It does allow a great deal of flexibility though with pattern matching in strings.

Regex Check = ReplaceRegex(my column,"(.*)0([0-9]{3}\-[a-zA-Z]{4}\-[a-zA-Z]{3,4})(.*)","$1D$2$3")

(.*) (ie. $1 and $3) = just match any character.
([0-9]{3}\-[a-zA-Z]{4}\-[a-zA-Z]{3,4}) (ie. $2) = match 3 digits followed by "-" followed by any 4 alphabet characters followed by "-" followed by any 3-4 alphabet characters

The formula will return the origin column if it does not match on your pattern strings, so you may need to use an if statement afterwards.

You can control where in the string you want to match the patterns and a whole lot more.

Another excellent idea, @Mark Johnson. I've only used ReplaceRegex() once, to get rid of special characters. It is indeed very powerful.​

Thank You!
Original Message:
Sent: 09-30-2022 00:46
From: Mark Johnson
Subject: Logic

I have always like the ReplaceRegex formula although it can be performance heavy, so do use with some caution. It does allow a great deal of flexibility though with pattern matching in strings.

Regex Check = ReplaceRegex(my column,"(.*)0([0-9]{3}\-[a-zA-Z]{4}\-[a-zA-Z]{3,4})(.*)","$1D$2$3")

(.*) (ie. $1 and $3) = just match any character.
([0-9]{3}\-[a-zA-Z]{4}\-[a-zA-Z]{3,4}) (ie. $2) = match 3 digits followed by "-" followed by any 4 alphabet characters followed by "-" followed by any 3-4 alphabet characters

The formula will return the origin column if it does not match on your pattern strings, so you may need to use an if statement afterwards.

You can control where in the string you want to match the patterns and a whole lot more.