Removing a zero in the third place of a text string if found

Scott Bovaird

I have data that the zero varies where it is located. Some examples would be AA01 or AA01x as well as AA10, AA10x, etc.

The first two characters are always letters and I want to return the rest of string without a zero in the third place. So if the first column is AA01x, I want the next column to display AA1x. While if the first column has AA10, I want it to return AA10.

I tried =SUBSTITUTE(Column@row, "0", ""), but when I have AA10 or AA01 it returns AA1 in both cases. I need it to display AA10 & AA1.

Thanks in advance!

Darren Mullen

@Scott Bovaird

This formula will work assuming there are always at last numbers after the AA

=SUBSTITUTE(LEFT(Column@row, 3), "0", "") + MID(Column@row, 4, LEN(Column@row) - 3)

Darren Mullen

@Scott Bovaird

This formula will work assuming there are always at last numbers after the AA

=SUBSTITUTE(LEFT(Column@row, 3), "0", "") + MID(Column@row, 4, LEN(Column@row) - 3)

Hunter Taylor

Hi Scott,

To solve your problem, you need a formula that specifically checks if the "0" is in the third position of the string and removes it only in that case. Here's how you can do it:

Use this formula in the desired column:

=IF(MID(Column@row, 3, 1) = "0", LEFT(Column@row, 2) & MID(Column@row, 4, LEN(Column@row)), Column@row)

Explanation:

1. MID(Column@row, 3, 1) extracts the third character from the string.
2. IF checks if that third character is "0".
3. If it is "0", it concatenates:
   • The first two characters: LEFT(Column@row, 2)
   • The rest of the string starting from the fourth character: MID(Column@row, 4, LEN(Column@row))
4. If the third character is not "0", it simply returns the original string: Column@row.

Example Output:

• Input: AA01x → Output: AA1x
• Input: AA10 → Output: AA10
• Input: BB02y → Output: BB2y
• Input: CC20 → Output: CC20

This ensures that only a "0" in the third position is removed, while other parts of the string remain unaffected. Let me know if you need further assistance!

Scott Bovaird

I get "#UNPARSEABLE" with the following formula - =IF(MID([Primary Column]@row, 3, 1) = "0", LEFT([Primary Column]@row, 2) MID([Primary Column]@row, 4, LEN([Primary Column]@row)), [Primary Column]@row)

I do have other text on the back end as well (-1 / -2 / etc.). See below screenshot

I am 100% sure it is me.. HAHA Thanks again!

Scott Bovaird

=SUBSTITUTE… Worked..

Thanks again!!