Add a leading letter to existing column values IF

gjohnson.pcpro

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!

Kevin Smith

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.

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.

gjohnson.pcpro

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

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.

gjohnson.pcpro

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