Using LEFT, MID, RIGHT Functions

Hi,
If we have organization codes in (7) or (8) digits and we want to modify these codes to read (00-000-000), we can make the required modification in two steps.
Step (1):
We add (0) to the (7) digits by the following formula:
=IF(LEN(A2)=7;"0"&A2;A2)



Step (2):
We add two(-) to the (8) digits by the following formula:
=LEFT(B2;2)&"-"&MID(B2;3;3)&"-"&RIGHT(B2;3)

• Right click on data in A column
• Format cells
• Custom
• Type: 00-000-000

Hi, zbor
Thank you for your suggestion, the reason for the long example that I used in my tip is to explain to Excel users (LEFT, MID, RIGHT Functions).
Best regards.

Originally Posted by IMA_Saihat

Hi, zbor
Thank you for your suggestion, the reason for the long example that I used in my tip is to explain to Excel users (LEFT, MID, RIGHT Functions).
Best regards.

It's OK to offer alternatives so beside learning mentioned functions everyone can also see some other approach.

For example, your formula in Step 1 can be created without IF function as:

Formula:

Please Login or Register  to view this content.

But you also don't need helper column for such thing and you can shorten it into:

Formula:

Please Login or Register  to view this content.

Hi, zbor
Thank you very much, I like your Formula =TEXT(RIGHT(0&A2, 8), "00-000-000"), As I mentioned before "This is the good advantage of Excel - many methods for one result".
Regards.

I agree with you.
Thanks for interesting posts.