troubleshooting Question

Custom format to align multiple values

Avatar of Rob Henson
Rob HensonFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft ExcelMicrosoft Office
3 Comments1 Solution21 ViewsLast Modified:

I am using TEXTJOIN function to concatenate a number of values and related text in a variance analysis summary as below:
IF(Q32=0,"",$C$23&": "&TEXT(Q32/10^6," 0.0 ;(0.0)")&" "&R32),
IF(Q46=0,"",$C$37&": "&TEXT(Q46/10^6," 0.0 ;(0.0)")&" "&R46),
IF(Q60=0,"",$C$51&": "&TEXT(Q60/10^6," 0.0 ;(0.0)")&" "&R60),
IF(Q74=0,"",$C$65&": "&TEXT(Q74/10^6," 0.0 ;(0.0)")&" "&R74))

Open in new window

This looks at the values in the various cells in Q and takes Dept ID from C and shows with variance explanation from R; result being like below:

Dept1: (0.0) No Budget - Runs on actuals
Dept2: (0.5) No Budget - Runs on actuals
Dept3: (78.6) No Budget - Runs on actuals
Dept4:   0.1  No Budget - Runs on actuals

Open in new window

All Dept codes are the same length (4 characters) and values can be either positive or negative.

What I am trying to achieve is getting the values to be aligned at the decimal point. I am thinking that I can use the custom format within the TEXT function to pad as required. I only need one decimal point and the integer value should be no more than two digits. The result for above would be:
Dept1: ( 0.0) No Budget - Runs on actuals
Dept2: ( 0.5) No Budget - Runs on actuals
Dept3: (78.6) No Budget - Runs on actuals
Dept4:   0.1  No Budget - Runs on actuals

Open in new window

I am guessing it would be use of # and/or _ within the custom format but don't know what the syntax is for those.

Many thanks.
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 3 Comments.
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>


Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 3 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004