Replace "0." in text field

[vavs]

I have a table that was created from another table. The data in the column in the new table is TEXT. My problem is that some of the values are being treated as numbers, because they look like numbers. What I need to do is to save the cell as a text field without a leading zero.

The field looks like this right now 0.017, I need to make it .017

I have tried to convert the column to text and then replace 0. with . but it does not change the value at all.

This is critical as I am doing a match to this field and 0.017 and .017 do not match.

 

[WhatTheXL]

If all your data has 3 digits to the right of the .

Try:

=RIGHT(A1,4)

 

[Rick Rothstein]

Since you have a mix of values, try this...

=IF(LEFT(A1,2)="0.",MID(A1,2,99),A1)

 

[Rick Rothstein]

Since you have a mix of values, try this...

=IF(LEFT(A1,2)="0.",MID(A1,2,99),A1)

And to follow up, the above can be written a little more concisely like this...

=MID(A1,1+(LEFT(A1,2)="0."),99)

 

[vavs]

Those work and here is what I ended up using:

=IF(ISERROR(FIND("0.",$C79)),$C79,IF(FIND("0.",$C79)=1,REPLACE($C79,1,1,""),$C79))

I have some cells that contain data that does not need to be changed. In those cases, I just want to keep the value as is.


Thanks for all the suggestions and help.

 

[Rick Rothstein]

I have some cells that contain data that does not need to be changed. In those cases, I just want to keep the value as is.

To the best of my knowledge, both formulas that I posted (exactly as I posted them) already do that... give them a try against your actual data and see for yourself.