Skip to content

Instantly share code, notes, and snippets.

@ExcelRobot
Last active December 21, 2023 14:59
Show Gist options
  • Save ExcelRobot/c23bd23e8903dbadf70a1e445ed81ce8 to your computer and use it in GitHub Desktop.
Save ExcelRobot/c23bd23e8903dbadf70a1e445ed81ce8 to your computer and use it in GitHub Desktop.
Convert number to ordinal LAMBDA Function
/*
Name: Convert Number To Ordinal (ADDTH)
Description: Converts number to ordinal ie: 1st, 2nd, 3rd, 4th, 11th, 12th, 13th, 21st, etc.
Author: Excel Robot (@ExcelRobot)
Inspired By: Rick de Groot (https://www.linkedin.com/posts/rickmaurinus_powerbi-businessintelligence-dax-activity-6920723485937790976-Wzdb?utm_source=linkedin_share&utm_medium=member_desktop_web)
Category: Conversion
*/
ADDTH = LAMBDA(number,LET(
LastDigit, RIGHT(number),
LastTwo, RIGHT(number, 2),
Suffix, IFS(
OR(LastTwo="11",LastTwo="12",LastTwo="13"), "th",
LastDigit="1", "st",
LastDigit="2", "nd",
LastDigit="3", "rd",
TRUE, "th"
),
number & Suffix
));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment