Align decimal point when two numbers have different sign

[OldSwimmer1650]

I'm trying align the decimal points of these two numbers. The problem is when one is negative and the other positive they don't align. I would also like not to show leading zeros.
This is for a stock picking game for a finance class.

My expression is:
=CONCATENATE(XLOOKUP(MAX(Stocks!J17:J27),Stocks!J17:J27,CurrentStocks,,0)," ",TEXT(MAX(Stocks!J17:J27),"$0,000,000.00"))
(note: The expression is the same for both Winner & Looser except Min used for Looser and Max used for Winner)

I've also tried:
=CONCATENATE(XLOOKUP(MAX(Stocks!J17:J27),Stocks!J17:J27,CurrentStocks,,0)," ",TEXT( MAX(Stocks!J17:J27),"$#,000.00"))
(note: The expression is the same for both Winner & Looser except Min used for Looser and Max used for Winner)

 

[Joe4]

Shouldn't "Looser" be "Loser" ("Loser" is the opposite of "Winner" -- "Looser" mean "less tight" in the English language!")

Also for the last past, instead of:

TEXT(MAX(Stocks!J17:J27),"$#,000.00"))

I would use:

TEXT(MAX(Stocks!J17:J27),"$#,##0.00"))

or else you will have leading zeroes on any number between -999.99 and 999.99.

The issue that you are going to have is that if you combine all these in one cell with the CONCATENATE function, you are creating one long string.
If you want the numbers at the end to line up, then you have to do the following:
- choose a font where all letters are the same width
- count the number of characters in each of the three parts of your concatenate function, and pad them with extra spaces where necessary.

Of course, if you just kept these in 2 or 3 separate columns, so that the last part was a number and not text, they would line up nicely on their own.

 

[iggydarsa]

Can't you just use "Align Right" option?

 

[Joe4]

Can't you just use "Align Right" option?
View attachment 112330

Then the left-side of that string won't line up!
It looks to me like they want each of the 3 pieces of the string to line up (the first two left-justified and the number right-justified).
If it was me, I would probably just keep it as three separate cells and not mess around with messy formulas.

 

[OldSwimmer1650]

Shouldn't "Looser" be "Loser" ("Loser" is the opposite of "Winner" -- "Looser" mean "less tight" in the English language!")

Also for the last past, instead of:

TEXT(MAX(Stocks!J17:J27),"$#,000.00"))

I would use:

TEXT(MAX(Stocks!J17:J27),"$#,##0.00"))

or else you will have leading zeroes on any number between -999.99 and 999.99.

The issue that you are going to have is that if you combine all these in one cell with the CONCATENATE function, you are creating one long string.
If you want the numbers at the end to line up, then you have to do the following:
- choose a font where all letters are the same width
- count the number of characters in each of the three parts of your concatenate function, and pad them with extra spaces where necessary.

Of course, if you just kept these in 2 or 3 separate columns, so that the last part was a number and not text, they would line up nicely on their own.

Unfortunately, I only have one cell to work with. Diction and grammar are always the last things I work on. This is just an initial mock-up. Thank you for your input. I really appreciate it.

 

[OldSwimmer1650]

Can't you just use "Align Right" option?
View attachment 112330

If I do that the N and D on the left won't align.

 

[StephenCrump]

You could pad with blanks, and use a font with a fixed character width. I've used Courier New below, as it wouldn't work with my usual Arial font.

Book1
	A	B	C
1	Gain/Loss Winner	NVDA	1,328,872.79
2	Gain/Loss Loser	IIII	0.00
3	Gain/Loss Winner	XXXX	123.00
4
5	Gain/Loss Winner NVDA $1,328,872.79
6	Gain/Loss Loser IIII $0.00
7	Gain/Loss Winner XXXX $123.00
Sheet1

Cell Formulas
Range	Formula
A5:A7	A5	=LEFT(A1&" ",18)& B1 &RIGHT(REPT(" ",10)&TEXT(C1,"$#,##0.00"),15)

 

[OldSwimmer1650]

You could pad with blanks, and use a font with a fixed character width. I've used Courier New below, as it wouldn't work with my usual Arial font.

Book1
	A	B	C
1	Gain/Loss Winner	NVDA	1,328,872.79
2	Gain/Loss Loser	IIII	0.00
3	Gain/Loss Winner	XXXX	123.00
4
5	Gain/Loss Winner NVDA $1,328,872.79
6	Gain/Loss Loser IIII $0.00
7	Gain/Loss Winner XXXX $123.00
Sheet1

Cell Formulas
Range	Formula
A5:A7	A5	=LEFT(A1&" ",18)& B1 &RIGHT(REPT(" ",10)&TEXT(C1,"$#,##0.00"),15)

Thank you very much for the input. Worked fine! Key was the font.