need to add .0 after number (as 11 A to 11.0 A)

[sksanjeev786]

Hi Team

Need to add .0 if it is in whole number

Data.xlsx
	S	T	U
103	3.6	24.5	44 A
104	14.3	18.6	44.6 A
105	92.9	-3.2	7.1 A
106	67.9	0.8	-1.6
107	82.1	-7.9	-1.6
108	82.1	0.7	10.6
109	80.4	1.3	11
110	73.2	-4.5	11 A
111	80.4	-5.5	10.3 A
112	80.4	-3.5	14.4 A
113	75	-7.7	1.8
114	80.4	-1.8	11 A
1 (2)

 

[DanteAmor]

Try:

varios 09ene2024.xlsm
	S	T	U	V
103	3.6	24.5	44 A	44.0 A
104	14.3	18.6	44.6 A	44.6 A
105	92.9	-3.2	7.1 A	7.1 A
106	67.9	0.8	-1.6	-1.6
107	82.1	-7.9	-1.6	-1.6
108	82.1	0.7	10.6	10.6
109	80.4	1.3	11	11
110	73.2	-4.5	11 A	11.0 A
111	80.4	-5.5	10.3 A	10.3 A
112	80.4	-3.5	14.4 A	14.4 A
113	75	-7.7	1.8	1.8
114	80.4	-1.8	11 A	11.0 A
Hoja6

Cell Formulas
Range	Formula
V103:V114	V103	=IF(RIGHT(U103)="A",IF(--SUBSTITUTE(U103," A","")=INT(--SUBSTITUTE(U103," A","")),--SUBSTITUTE(U103," A","")&".0 A",U103),U103)

Excel 365:

=LET(a,--SUBSTITUTE(U103," A",""),IF(RIGHT(U103)="A",IF(a=INT(a),--a&".0 A",U103),U103))

If you want the ".0" in any value regardless of whether or not it has the letter "A" at the end, then:

=LET(b,U103,a,--SUBSTITUTE(b," A",""),IF(RIGHT(b)="A",IF(a=INT(a),--a&".0 A",b),IF(b=INT(b),b&".0",b)))

 

[sksanjeev786]

Try:

varios 09ene2024.xlsm
	S	T	U	V
103	3.6	24.5	44 A	44.0 A
104	14.3	18.6	44.6 A	44.6 A
105	92.9	-3.2	7.1 A	7.1 A
106	67.9	0.8	-1.6	-1.6
107	82.1	-7.9	-1.6	-1.6
108	82.1	0.7	10.6	10.6
109	80.4	1.3	11	11
110	73.2	-4.5	11 A	11.0 A
111	80.4	-5.5	10.3 A	10.3 A
112	80.4	-3.5	14.4 A	14.4 A
113	75	-7.7	1.8	1.8
114	80.4	-1.8	11 A	11.0 A
Hoja6

Cell Formulas
Range	Formula
V103:V114	V103	=IF(RIGHT(U103)="A",IF(--SUBSTITUTE(U103," A","")=INT(--SUBSTITUTE(U103," A","")),--SUBSTITUTE(U103," A","")&".0 A",U103),U103)

Excel 365:

=LET(a,--SUBSTITUTE(U103," A",""),IF(RIGHT(U103)="A",IF(a=INT(a),--a&".0 A",U103),U103))

If you want the ".0" in any value regardless of whether or not it has the letter "A" at the end, then:

=LET(b,U103,a,--SUBSTITUTE(b," A",""),IF(RIGHT(b)="A",IF(a=INT(a),--a&".0 A",b),IF(b=INT(b),b&".0",b)))

Hello Sir,

Thank you so much for your help.
Now still i can not see the decimal on the number i.e i need Cell V109 11 and i need as "11.0" also.

 

[DanteAmor]

If you want the ".0" in any value regardless of whether or not it has the letter "A" at the end, then:

Maybe you didn't see my update at the end of my post.

Try this for excel 365:

=LET(b,U103,a,--SUBSTITUTE(b," A",""),IF(RIGHT(b)="A",IF(a=INT(a),--a&".0 A",b),IF(b=INT(b),b&".0",b)))

 

[sksanjeev786]

Maybe you didn't see my update at the end of my post.

Try this for excel 365:

=LET(b,U103,a,--SUBSTITUTE(b," A",""),IF(RIGHT(b)="A",IF(a=INT(a),--a&".0 A",b),IF(b=INT(b),b&".0",b)))

Hi Sir,

I have checked withe above Formula still getting 11 instead of 11.0 i have bold in RHS table (used above formula

4	24.5	44.6 A		4.0	24.5	44.6 A
14	18.6	44.6 A		14.0	18.6	44.6 A
93	-3.2	7.1 A		93.0	-3.2	7.1 A
68	0.8	-1.6		68.0	0.8	-1.6
82	-7.9	4.7 A		82.0	-7.9	4.7 A
82	0.7	10.6		82.0	0.7	10.6
80	1.3	11		80.0	1.3	11
73	-4.5	11.2 A		73.0	-4.5	11.2 A
80	-5.5	10.3 A		80.0	-5.5	10.3 A
80	-3.5	14.4 A		80.0	-3.5	14.4 A
75	-7.7	1.8		75.0	-7.7	1.8
80	-1.8	11 A		80.0	-1.8	11.0 A

 

[Scott Huish]

=TEXT(SUBSTITUTE(U103," A",""),"0.0")&IF(RIGHT(U103)="A"," A","")

 

[sksanjeev786]

=TEXT(SUBSTITUTE(U103," A",""),"0.0")&IF(RIGHT(U103)="A"," A","")

 

[sksanjeev786]

This works perfectly to me!!

Thanks you team for you support on this