If/Then/Else - that I can't find answered anywhere in the community

Tom Vick

If cell A1 starts with "01-" then replace (the "01-") with "AAAA" else if cell A1 starts with "02-" then replace (the "02-") with "BBBB" else replace the first three characters in cell A1 with "CCCC". Thanks in advance! Tom

Bassam Khalil

Hi @Tom Vick

Hope you are fine, please try the following formula.

=IF(LEFT([Column7]@row, 3) = "01-", "AAAA-" + MID([Column7]@row, 4, 1000), IF(LEFT([Column7]@row, 3) = "02-", "BBBB-" + MID([Column7]@row, 4, 1000), "CCCC-" + MID([Column7]@row, 4, 1000)))

the following screenshot shows the result.

Bassam Khalil

Hi @Tom Vick

Hope you are fine, please try the following formula.

=IF(LEFT([Column7]@row, 3) = "01-", "AAAA-" + MID([Column7]@row, 4, 1000), IF(LEFT([Column7]@row, 3) = "02-", "BBBB-" + MID([Column7]@row, 4, 1000), "CCCC-" + MID([Column7]@row, 4, 1000)))

the following screenshot shows the result.

Andrée Starå

Hi @Tom Vick

I hope you're well and safe!

To add to Bassam's excellent advice/answer.

Here's another option. (we could also use SUBSTITUTE)

=IF(CONTAINS("01-"; [A1]@row); REPLACE([A1]@row; 1; 3; "AAAA"); IF(CONTAINS("02-"; [A1]@row); REPLACE([A1]@row; 1; 3; "BBBB"); REPLACE([A1]@row; 1; 3; "CCCC")))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Would that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!