How to keep accounting formatting for cell with formula

[kmsprague]

Hi all,

I have a formula in a cell that is working perfectly, but I can't keep the accounting formatting in the cell. I've tried the regular format cells option as well as conditional formatting, but I can't keep the left justified $ like I want. Any suggestions?

Formula:

Result:

Desired Result:

 

[dreid1011]

I imagine it is losing the formatting because you are forcing it to be text. Perhaps you can try this:

=LET(maxlength, 15,text, TEXT(IF($F$17="Employee Only","1250","2500")," $ #,##0.00__ ; $ (#,##0.00)_ ; $ ""-    """),length, LEN(text),diff, MAX(0,maxlength-length),newtext, SUBSTITUTE(text,"$ ","$"&REPT(" ",diff)),newtext)

Formula found here: 6 Ways to Add Accounting Number Format in Microsoft Excel | How To Excel
I changed the D3 reference in the original formula to your IF formula.

 

[kmsprague]

I imagine it is losing the formatting because you are forcing it to be text. Perhaps you can try this:

=LET(maxlength, 15,text, TEXT(IF($F$17="Employee Only","1250","2500")," $ #,##0.00__ ; $ (#,##0.00)_ ; $ ""-    """),length, LEN(text),diff, MAX(0,maxlength-length),newtext, SUBSTITUTE(text,"$ ","$"&REPT(" ",diff)),newtext)

Formula found here: 6 Ways to Add Accounting Number Format in Microsoft Excel | How To Excel
I changed the D3 reference in the original formula to your IF formula.

Hi dreid! This is SO close to what I need! But it's still not justifying the $ all the way to the left. I know it's such a small thing, but it looks out of place next to the numbers in other cells.

 

[kmsprague]

I imagine it is losing the formatting because you are forcing it to be text. Perhaps you can try this:

=LET(maxlength, 15,text, TEXT(IF($F$17="Employee Only","1250","2500")," $ #,##0.00__ ; $ (#,##0.00)_ ; $ ""-    """),length, LEN(text),diff, MAX(0,maxlength-length),newtext, SUBSTITUTE(text,"$ ","$"&REPT(" ",diff)),newtext)

Formula found here: 6 Ways to Add Accounting Number Format in Microsoft Excel | How To Excel
I changed the D3 reference in the original formula to your IF formula.

Ok, I added some spaces in the formula between the $ and the # and now it looks good!! Thank you so much, dreid!

 

[Alex Blakenburg]

I don't understand why you are putting the numbers in as text. What happens if instead
Instead of "1,250.00","2,500.00" you just put 1250,2500 and then copy the formatting from a cell that looks the way you want it

 

[kmsprague]

I don't understand why you are putting the numbers in as text. What happens if instead
Instead of "1,250.00","2,500.00" you just put 1250,2500 and then copy the formatting from a cell that looks the way you want it

Alex, you are right! This works perfectly! Thank you!

 

[Alex Blakenburg]

It looks like I just pipped @MARK858 at the post
Thanks for letting us know. Glad that worked for you.

 

[dreid1011]

It looks like I just pipped @MARK858 at the post
Thanks for letting us know. Glad that worked for you.

Feeling silly I didn't think of that myself! Though satisfied to have found a workable solution, if way over engineered.