Cell with Format in text needs to show 10.0

[Tartesos]

I have a question and I hope that you guys can help me with it.
I have a column that contains the Format Cell on Text.
The problem is that I need a way to paste data(numbers) on those cells that are containing a .0 between them. When I put my data on that column it remove the .0
Lest say I have in a sheet the data 10.0 and 2.0 and when I copy them to this column the .0 is not there any more.
I know that if I change the Format Cell to numbers it will be solve but the problem is that this sheet we are using only can accept text. This sheet is part of as sistem that cant change the Cell Format, is stupid but that is what Im dealing with. So, there is any other way for me to keep the 10.0, 2.0 or whatever in that format ??
I have thousend of numbers that I need to change if I dont find a smart way to do it.
Any help with be great

Thanks

 

[JackDanIce]

Tartetsos, I have to admit, I don't find your questions particularly clear or easy to understand which makes deciphering what you need help with difficult to say the least. Have a look at this very simple example of force formatting in a cell and try to adapt it to your own needs:

Cells A1 and A2 contain the numbers 10 and 2 respectively
Cells A3 and A4 multiply them together, but the output is forced to exclude or include ".0"

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 65px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: left">20</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">20.0</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A3</TD><TD>=A1*A2</TD></TR><TR><TD>A4</TD><TD>=TEXT(A1*A2,"0.0")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[GlennUK]

Put the text conversion into that formula, like:
=IF(ISNA(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),data!$C$4:$C$2488)),"",TEXT(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488, ....,"0.0") etc etc

I couldn't see the end of your formula, but I hope you get the idea.

 

[Tartesos]

Here is the full formula:
=IF(ISNA(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),data!$C$4:$C$2488)),"",LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),data!$C$4:$C$2488))

I tried what you told me but is not working. Can you help me with it ??

Regards,

 

[GlennUK]

How about:
=IF(ISNA(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),data!$C$4:$C$2488)),"",TEXT(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),data!$C$4:$C$2488),"0.0"))

 

[Tartesos]

Thanks man that did the trick, "Bull eye" ... thanks

Regards

 

[GlennUK]

That's great!