Problem with TEXT formula/format

XL9149

New Member
Joined
Dec 20, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Hopefully I'm just being dense and this will be a simple one for you.

Cell A1 contains a formula that produces 1 of 2 results; '1,000' (one-thousand) or '1,000,000' (one million). Used for rounding purposes in a reporting document.

Cell A2 contains the formula
Excel Formula:
="'"&TEXT(RIGHT($A$1,LEN($A$1)-1),"000")&"s"
which is intended to show the results in a text format. It SHOULD generate either the text '000s or '000,000s if the result is of A1 is 1,000 or 1,000,0000 respectively.

The problem I'm having is that regardless of the result in cell A1, the result in A2 remains '000s . This is the correct result if the result in cell A1 is 1,000, but it remains so even if the result in A1 is 1,000,000 - in this case A2 should generate a result of '000,000.

I know the issue is with the prescribed text format, but I've been staring at my screen for too long to be able to figure out why and my brain is suffering from Excel bloat :)
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Thanks, but the issue with that is that there is no comma between every three zeros. The result generated in A2 when A1 shows 1,000,000 is '000000s when I would need it to shows '000,000s
 
Upvote 0
How about
Excel Formula:
="'"&RIGHT(TEXT($A$1,"###,##0"),LEN(TEXT($A$1,"###,##0"))-2)&"s"
 
Upvote 0
Thanks to all for your input.

Fluff - I tried your formula and it generated a value error (probably because in my attempt to simplify the query, I omitted that in place of $A$1, I'm using an INDIRECT formula to reference the actual cell)
Jasonb75 - your solution works perfectly. Thank you
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top