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 :)
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

XL9149

New Member
Joined
Dec 20, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,805
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
="'"&RIGHT(TEXT($A$1,"###,##0"),LEN(TEXT($A$1,"###,##0"))-2)&"s"
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Perhaps
Book1 (version 1).xlsb
AB
11000'000s
210000'000s
3100000'000s
41000000'000,000s
510000000'000,000s
6100000000'000,000s
71000000000'000,000,000s
Sheet3
Cell Formulas
RangeFormula
B1:B7B1="'"&MID(TEXT(A1,"#0,000"),FIND(",",TEXT(A1,"#0,000"))+1,255)&"s"
 
Solution

XL9149

New Member
Joined
Dec 20, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,805
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,165
Messages
5,629,068
Members
416,363
Latest member
zaveedd

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
Top