Help with Excel function

P

Petal

Registered User
Messages
881
Dear All,
Hopefully something has an idea...
I have an excel spreadsheet with conditional formatting, as I have values ranging from 0.0000012 to 800 and I've put on conditional formatting on the number of decimals.
Now I want to combine values from two columsn in one by using something like
= A1 & " - " & B1 so that I get something like 0.008 - 6.7
the problem is that I am loosing my conditional formatting in this process and what I'm getting is
0.0089632541 - 6.742369871
I know I can use the round function, but since every cell has a different amount of decimals I would get very very old in the process. So I'm wondering is there anything that I can put into the above formula that will make it retain the original number formatting?
Would be ever so grateful if someone had a solution....
 
AFAIK concatenating (use of &) will joint two strings so after concatenation you don't have a NUMBER, it is text! Therefore number formatting won't/can't apply...
You could use something like
=LEFT(A1,5)&" - "&LEFT(B1,3) would result in 0.008 - 6.7
=LEFT(A1,6)&" - "&LEFT(B1,5) which would result in 0.0089 - 6.742
But the result(s) is/are TEXT not numbers...
HTH
 
Many thanks for this. The only problem is that every single cell is different, so I wouldn't save much time having to specify for every cell how many datapoints I want in the output. So I'm still searching for this miracle function :)
 
Well you didn't set a conditional formatting rule for each cell to get the original result. Depending on how many conditional formatting rules you had you could possibly adapt the LEFT formulae to mirror the results...
 
Well, I have 1888 cells with data in 5 sheets, so a LOT of cells. The results are anywhere from 0.0000008 - 300, and depending on the result I only want a specific amount of decimals, like zero for numbers above 1 and 1 decimal for numbers between 0.1 and 1, etc... so that's why I used conditional formatting, so that every single cell, depeding on it's value would get a different amount of decimals. So with this formatting, your formula wouldn't really work on a drag basis, as I would have to change the digit selector for every single cell. But I have found the answer in the meantime, which is removing all conditional formatting from the sheet and using a text function with a conditional formatting built in, so this now looks like

=TEXT(G29,IF(G29>10,ROUND(G29,0),IF(G29>1,ROUND(G29,1),IF(G29>0.1,ROUND(G29,1),IF(G29>0.01,ROUND(G29,2),IF(G29>0.001,ROUND(G29,3),IF(G29>0.0001,ROUND(G29,4),IF(G29>0.00001,ROUND(G29,5),ROUND(G29,6)))))))))&" - "&TEXT(H28,IF(H28>10,ROUND(H28,0),IF(H28>1,ROUND(H28,1),IF(H28>0.1,ROUND(H28,1),IF(H28>0.01,ROUND(H28,2),IF(H28>0.001,ROUND(H28,3),IF(H28>0.0001,ROUND(H28,4),IF(H28>0.00001,ROUND(H28,5),ROUND(H28,6)))))))))

Bit of a monster formula but given the amount of data I'm working on, it's the business!
 
Actually,

Just in case anyone ever wants to use this - I have tested this now, and I get some weird error responses (0.08 being rounded to 0.18 for some strange reason), but eventually I realised that when using the if function, I don't need the Text function at all, and then it works just fine and dandy!
 
You must log in or register to reply here.
Back
Top