Excel Text Function

What formula could I use to convert a string to a text field with 4 characters? In Excel, I use a simple Text() formula: =text(A1,"0000") where the following values would convert if...

A1 equals 123 and the output adds a leading '0' to '0123'
A1 equals 001234 and the output removes the leading '0' to '1234'

Tips and Tricks

Accepted answers

cplewis90

One additional modification:

If length([field])>4

then padleft(trimleft([field],"0"),4,"0")

else padleft([field],4,"0")

endif

Let me know if this works. If not I can try something else.

All comments

cplewis90

Hello @tarichar,

You would have to do an if statement to check the length and run different formulas depending on what is there. It would look something like the below:

If length([field])>4

then right([field], 4)

else padleft([field],4,"0")

endif

tarichar

Thanks for the feedback.

What if i also have values that are greater than 4 characters that I need to stay the same? Example, 123456 needs to remain 123456, not convert to '3456' (using the above if statement).

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703