Number format to show only trailing zeros

[sparky2205]

My apologies if this question has been posed elsewhere. I have been trawling looking for an answer but all I can find are partial solutions.
My issue:
I want to convert a number to text and display all trailing zeros.
Simple enough you might say.
Here's the catch. The number to be converted, let's say 15.40, isn't actually 15.40. It was entered into the cell as 15.40 but of course Excel chopped off the trailing zero and it became 15.4. Then the cell was formatted to display 15.40 again. Of course Excel still sees this number as 15.4.
My reason for wanting this:
I have a column containing a mixture of nearly anything, numbers and text. Numbers can be with or without decimals, various lengths, contain various numbers of leading and trailing zeros etc.
Using a formula I have to transfer each of these values to another worksheet. The formula is using INDEX MATCH MATCH to obtain the values then converting the values to text. Using IF statements to identify integers and with a mix of =TEXT(cell,0) and =TEXT(cell,"0.######") I can satisfy all the criteria bar the trailing zeros. "0.00" won't work as this fills to exactly 2 decimal places. "0.000000" won't work as this fills to exactly 6 decimal places etc. I need the number of decimal places to be variable.
Is there a number format somewhere that I am missing?
Is there some way of forcing Excel to see the displayed number in a cell as the actual value of the cell?

I hope this is clear. Any help much appreciated.

Regards,
Joe.

 

[RoryA]

Is code an option? I think it will be by far the simplest solution.

 

[sparky2205]

Perhaps if the code were a function that I could use as part of a formula. The problem is it's not just a simple =TEXT(A1,"some format") in cell A1 of another worksheet. I have a matrix of data which I search using INDEX MATCH MATCH. The returned data must then be handled depending on it's format i.e. text, blank, integer/not integer etc.
This is the formula I am currently working with:
=IF(ISBLANK(L9),"",UPPER(IF(ISNUMBER(L9),IF(INT((INDEX($K$8:$O$16,MATCH($K9,$K$8:$K$16,0),MATCH(L$8,$K$8:$O$8,0))))=INDEX($K$8:$O$16,MATCH($K9,$K$8:$K$16,0),MATCH(L$8,$K$8:$O$8,0)),TEXT(INDEX($K$8:$O$16,MATCH($K9,$K$8:$K$16,0),MATCH(L$8,$K$8:$O$8,0)),0),TEXT(INDEX($K$8:$O$16,MATCH($K9,$K$8:$K$16,0),MATCH(L$8,$K$8:$O$8,0)),"#0.######")),TEXT(L9,0))))
As I said it works fine for all scenarios I have currently tested except for trailing zeros.
When you said code would be the simplest solution did you mean simplest or only?

Thanks,
Joe.

 

[RoryA]

You can use a UDF like:

Function CellText(rIn As Range) As String
    Application.Volatile True
    CellText = rIn.Cells(1).Text
End Function

The downside to this is that it won't automatically react to you changing the format of a cell (it will react if the contents change).

Offhand, I can't think of a non-VBA solution, but there may be one.

 

[sparky2205]

I think this is one I'll have to work around Rory.
The core of the issue comes down to Excel not recognising the formatted text.

Thanks very much for your efforts Rory.

Regards,
Joe.

 

[RoryA]

That function will return the formatted text as displayed in the cell.

 

[sparky2205]

Ah right. I misunderstood. It displays the formatted text but if you change the cell format this function will not update. It only updates if the value in the cell changes.
I'll see if I can incorporate this into my formula. I'll try it out and let you know.

Thanks again RoryA.

 

[sparky2205]

Hello again,
I just checked this out. It works exactly as you said it would.
Even though it doesn't automatically update when the cell format changes I can easily achieve an update using F9.
It also updates with a change in value to any other spreadsheet cell.
I think I can achieve what I want with this.
Thanks again for your help RoryA.

Regards,
Joe.