SOLVED
Home

suffix in cells with custom format

%3CLINGO-SUB%20id%3D%22lingo-sub-1119482%22%20slang%3D%22en-US%22%3Esuffix%20in%20cells%20with%20custom%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1119482%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EGood%20morning%20I%20ask%20you%20for%20help%2C%20I%20need%20to%20add%20a%20suffix%20to%20a%20value%20read%20in%20the%20cell%20and%20I'm%20using%20the%20custom%20format%20by%20adding%20the%20following%20wording%20%22text%22%200.0%20but%20when%20the%20value%20stored%20in%20the%20cell%20is%20negative%20the%20result%20is%20this%3A%20-text%3C%2FSPAN%3E%20%3CSPAN%3E1.8%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3Eis%20there%20any%20way%20to%20solve%20the%20problem%20that%20the%20minus%20sign%20is%20placed%20before%20the%20suffix%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1119482%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1119496%22%20slang%3D%22en-US%22%3ERe%3A%20suffix%20in%20cells%20with%20custom%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1119496%22%20slang%3D%22en-US%22%3EKindly%20see%20the%20link%20below%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.exceljetconsult.com.ng%2Fhome%2Fblog%2Fcustom-number-formatting-how-to-add-prefix-suffix%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.exceljetconsult.com.ng%2Fhome%2Fblog%2Fcustom-number-formatting-how-to-add-prefix-suffix%2F%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1119568%22%20slang%3D%22en-US%22%3ERe%3A%20suffix%20in%20cells%20with%20custom%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1119568%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Ethanks%20but%20my%20problem%20is%20that%20I%20have%20to%20put%20both%20a%20prefix%20and%20a%20suffix%2C%20when%20I%20read%20an%20example%20value%2027.5%20I%20have%20to%20view%20it%20like%20this%3A%20Temp%20%3D%2027.5%20%C2%B0%20C%20where%20the%20prefix%20is%20%22Temp%20%3D%22%20and%20the%20suffix%20is%20%22%C2%B0%20C%22.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3Ehowever%2C%20when%20the%20value%20read%20is%20negative%20it%20is%20displayed%20as%3A%20-%20Temp%20%3D%2027.5%20%C2%B0%20C%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1119927%22%20slang%3D%22en-US%22%3ERe%3A%20suffix%20in%20cells%20with%20custom%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1119927%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F526634%22%20target%3D%22_blank%22%3E%40GMettew20%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20is%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%22Temp%20%22%20%23%2C%23%230.0%20%22dgC%22%3B%22Temp%20%22-*%20%23%2C%23%230.0%20%22dgC%22%3B%22Temp%20%22%20%23%2C%23%230.0%20%22dgC%22%3B_-%40_-%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1120111%22%20slang%3D%22en-US%22%3ERe%3A%20suffix%20in%20cells%20with%20custom%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1120111%22%20slang%3D%22en-US%22%3ELets%20say%20you%20have%2023.4%20in%20cell%20A1%3CBR%20%2F%3E1.%20Select%20cell%20A1%3CBR%20%2F%3E2.%20Press%20CTRL%20%2B%2013.%20Select%20Custom%20%3CBR%20%2F%3E4.%20Clear%20General%20(below%20Type)%3CBR%20%2F%3E5.%20Type%20in%3A%20%22Temp%20%3D%20%22%23%2C%23%230.0%3CBR%20%2F%3E6.%20Then%2C%20hold%20down%20the%20AltGr%20key%20on%20your%20keyboard%20and%20type%200176C.%20(That's%20the%20code%20to%20have%20the%20degree%20symbol%20as%20seen%20in%20the%20attached%20pictures.%3CBR%20%2F%3EClick%20OK%3CBR%20%2F%3E%3CBR%20%2F%3ECompleted.%20See%20the%20two%20attachment%20%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1120136%22%20slang%3D%22en-US%22%3ERe%3A%20suffix%20in%20cells%20with%20custom%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1120136%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20not%20enough%2C%20you%20need%20to%20define%20negative%20part%20of%20the%20custom%20format%20string.%20Desirably%20zero%20part%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1120238%22%20slang%3D%22en-US%22%3ERe%3A%20suffix%20in%20cells%20with%20custom%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1120238%22%20slang%3D%22en-US%22%3EYou're%20absolutely%20correct..%3CBR%20%2F%3EBelow%20is%20both%20negative%20and%20positive%20with%20the%20prefix%20and%20the%20suffix%3CBR%20%2F%3E1.%20Select%20all%20the%20number%3CBR%20%2F%3E2.%20CTRL%20%2B%201%20and%20select%20Custom%3CBR%20%2F%3E3.%20In%20the%20General%2C%20executive%20the%20logical%20custom%20formatting%3CBR%20%2F%3E%5B%26gt%3B0%5D%22Temp%20%3D%20%22%23%2C%23%230.0oC%3B%5B%26lt%3B0%5D%22Temp%20%3D%20%22%23%2C%23%230.0oC%20%3CBR%20%2F%3E(ALTGR%20key%200176C%20to%20get%20the%20degree%20symbol).%20See%20the%20attached%20caption.%3CBR%20%2F%3E%3CBR%20%2F%3EWhen%20the%20numbers%20are%20in%20negative%2C%20you%20will%20see%3A%20-Temp%205.5oC%20(for%20example)%3CBR%20%2F%3EWhen%20it%20is%20positive%3A%20Temp%2023.5oC%20(for%20example)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1120303%22%20slang%3D%22en-US%22%3ERe%3A%20suffix%20in%20cells%20with%20custom%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1120303%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%2C%20I%20meant%20minus%20shall%20be%20before%20number%2C%20not%20before%20%22Temp%22%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20567px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F166309i8688EAE7785E09F4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1120329%22%20slang%3D%22en-US%22%3ERe%3A%20suffix%20in%20cells%20with%20custom%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1120329%22%20slang%3D%22en-US%22%3EI%20actually%20placed%20the%20minus%20before%20Temp%20as%20the%20requester%20put%20it...%20I%20can%20replace%20the%20equal%20sign%20with%20minus%20sign!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1121185%22%20slang%3D%22en-US%22%3ERe%3A%20suffix%20in%20cells%20with%20custom%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1121185%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%2C%20I%20understood%20the%20question%20by%20opposite%20way%2C%20but%20let%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F526634%22%20target%3D%22_blank%22%3E%40GMettew20%3C%2FA%3E%26nbsp%3Bdecides.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1120333%22%20slang%3D%22en-US%22%3ERe%3A%20suffix%20in%20cells%20with%20custom%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1120333%22%20slang%3D%22en-US%22%3EYou%20method%20to%20achieve%20the%20same%20result%20is%20quite%20interesting%3C%2FLINGO-BODY%3E
GMettew20
New Contributor

Good morning I ask you for help, I need to add a suffix to a value read in the cell and I'm using the custom format by adding the following wording "text" 0.0 but when the value stored in the cell is negative the result is this: -text 1.8. is there any way to solve the problem that the minus sign is placed before the suffix?

13 Replies
Highlighted
Highlighted

 

thanks but my problem is that I have to put both a prefix and a suffix, when I read an example value 27.5 I have to view it like this: Temp = 27.5 ° C where the prefix is "Temp =" and the suffix is "° C".
however, when the value read is negative it is displayed as: - Temp = 27.5 ° C

Highlighted

@GMettew20 

That is like

"Temp " #,##0.0 "dgC";"Temp "-* #,##0.0 "dgC";"Temp " #,##0.0 "dgC";_-@_-
Highlighted
Lets say you have 23.4 in cell A1
1. Select cell A1
2. Press CTRL + 1
3. Select Custom
4. Clear General (below Type)
5. Type in: "Temp = "#,##0.0
6. Then, hold down the AltGr key on your keyboard and type 0176C. (That's the code to have the degree symbol as seen in the attached pictures.
Click OK

Completed. See the two attachment
Highlighted

@Abiola1 

That's not enough, you need to define negative part of the custom format string. Desirably zero part as well.

Highlighted
You're absolutely correct..
Below is both negative and positive with the prefix and the suffix
1. Select all the number
2. CTRL + 1 and select Custom
3. In the General, executive the logical custom formatting
[>0]"Temp = "#,##0.0oC;[<0]"Temp = "#,##0.0oC
(ALTGR key 0176C to get the degree symbol). See the attached caption.

When the numbers are in negative, you will see: -Temp 5.5oC (for example)
When it is positive: Temp 23.5oC (for example)
Highlighted

@Abiola1 , I meant minus shall be before number, not before "Temp"

image.png

 

Highlighted
I actually placed the minus before Temp as the requester put it... I can replace the equal sign with minus sign!
Highlighted

@Abiola1 , I understood the question by opposite way, but let @GMettew20 decides.

Highlighted

@Sergei Baklan 

it is correct I needed this result:  Tevap = -27.5 °C

I have studied your answer and i reproduce it correctly:

"Tevap ="_?-* #.##0,00 "°C";"Tevap ="?-* #.##0,00 "°C";

the only problem is the alignmet, the result si infact :  Tevap= -        27,5°C and it epends froma the dimension of column, but it is not very important.

Thankyou for your answer 

 

Highlighted
Well, I only provide solution to the question the best way I understand it and for me, its not a question of "let so so decide". After all, this is not a competition for me
Highlighted
Solution

@Abiola1 

Of course. That's not about the Excel, that's about English: how to interpret

...when the value stored in the cell is negative the result is this: -text 1.8. is there any way to solve the problem that the minus sign is placed before the suffix?

Highlighted

@Abiola1 

I'm sorry but, since I don't speak English well, I probably explained myself wrong. However, your advice has helped me. Thanks anyway

 

Related Conversations