Making an 11 digit reference number that combines a date and a dollar amount,.

Hello,

I tried to come up with a nested formula that combines a deposit date and deposit amount into an 11 digit number. The given deposit date (Cell A2) is in mm/dd/yyyy. It needs to be converted into mmddyy format (6 digits) where the decade year is a zero no matter what. The dollar amount needs to have the decimal point removed and trailing zeros added if necessary to make the remaining 5 digits.

Examples:

Deposit Date: 05/21/2021, Deposit Amount: $1,234.56, Reference Number: 05210112345

Deposit Date: 11/21/2021, Deposit Amount: $504.21, Reference Number: 11210150421

Deposit Date: 12/01/2020, Deposit Amount: $10.24, Reference Number: 12010010240

Deposit Date: 01/20/2022, Deposit Amount: $5.67, Reference Number: 01200256700

Deposit Date: 05/22/2023, Deposit Amount: $0.05, Reference Number: 05220350000

I made a crazy sloppy macro that used many helper columns of single formulas that removed the brackets from the dates and made the decade portion of the yy a zero, highlighted the date column to change the format to mmddyy format, multiplied the Deposit Amount by 100 to removed the decimal point, add trailing zeros if necessary, and concatenate the results. My results were ok, but for any amounts $9.99 or less, the resulting reference number had to have one or more zeros added to make the 11 digits.

Was just hoping there was an amazing nested formula out there that could do the reference number making in one step.

Thanks.

[QUOTE=Phuocam;5597548]Try this:

=REPLACE(TEXT(A2,"mmddyy"),5,1,0)&LEFT(B2*1000000,5)[/QUOTE)

Thank you so much! The formula is beautiful! You’re a Rock Star!