Add a leading letter to existing column values IF

Good morning,

I have a column that contains project ID numbers. I want each project ID to begin with the letter M. Some of the values in this column already begin with M - for example, M12345. Other values in the column do not begin with M - for example, 12345.

I would like to apply a formula that will add a leading M to each value if the value does not already begin with M. Is this possible?

Thank you!

Best Answer

  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭
    Answer ✓

    Hi

    Glad that works.

    If you want to remove the M in case the ID is blank use this:

    =IF(CONTAINS("M", [Project ID]@row), [Project ID]@row, IF(ISBLANK([Project ID]@row), "", "M" + [Project ID]@row))

    I hope that helps.

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

Answers

  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭

    Hi

    This answer does depend on the origination of the Project ID numbers. However, assuming you can have some control and do not wish to change the the Orig. Project ID contents, you can add a new col. and use this formula:

    =IF(CONTAINS("M", [Project ID]@row), [Project ID]@row, "M" + [Project ID]@row)

    Keep in mind of the lonely "M" bothers you, you need to also check that the Project ID is not blank.

    Also, you can solve this by looking to see if Project ID is a number, if not, and there are not other Characters in the Project ID, then you may be able to assume there is already an "M".

    Would need to know more to make sure this solution works for you and there are more ways to solve depending on your requirements. You may be get other great ideas from the community.

    I hope this helps.

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • Kevin,

    Thank you so much. This is exactly what I needed. One final question. You mentioned checking that the Project ID is not blank. Some of the fields are indeed blank, and that results in the lonely M. Is there a way to omit blank values from the formula?

    Thank you again for your help. Much appreciated!

  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭
    Answer ✓

    Hi

    Glad that works.

    If you want to remove the M in case the ID is blank use this:

    =IF(CONTAINS("M", [Project ID]@row), [Project ID]@row, IF(ISBLANK([Project ID]@row), "", "M" + [Project ID]@row))

    I hope that helps.

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • Perfect. Thank you so, so much, Kevin!. I can't tell you how much I appreciate your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!