Keeping Excel from removing your zeroes...

[Isara]

One of the most annoying things about Excel is how it tries to be "helpful" by removing your trailing zeroes, even in a column formatted as Text. So I have a column that is text formatted that looks like this:

1957.310_det1.jpg
1957.310_det2.jpg
1957.310_det3.jpg
1957.310_det4.jpg
1957.312
1957.312_det1.jpg
1957.312_det2.jpg
1957.312_det3.jpg
1957.312_det4.jpg
1957.314
1957.315
1957.315_det1.jpg
1957.315_det2.jpg
1957.315_det3.jpg
1957.315_det4.jpg
1957.318
1957.318_det1.jpg
1957.318_det2.jpg
1957.319
1957.319_det1.jpg
1957.319_det2.jpg
1957.320
1957.320_det2.jpg
1957.320_det4.jpg

I was trying to strip out the underscores and everything after them and then delete the duplicates. But if I just perform a replace of _*, then I get this result:

1957.31
1957.31
1957.31
1957.31
1957.312
1957.312
1957.312
1957.312
1957.312
1957.314
1957.315
1957.315
1957.315
1957.315
1957.315
1957.318
1957.318
1957.318
1957.319
1957.319
1957.319
1957.320
1957.32
1957.32

... which means I can't strip out the duplicates easily.

Since the numbers are irregular, I can't tell excel that it should always have a certain number of training zeroes. Anyone have a handy solution for this?

[Moo the Dog]

I applied a Number format with 3 decimal places on the whole range and then did the replace of _* and it seemed to work fine. Worth a try?

- Moo

[Isara]

That would work, except that it's not always three decimal places. Sometimes it looks like this:

1959.7
1959.7_det2.jpg
1959.7_det9.jpg
1959.7.tif
1959.8_det.jpeg
1959.8
1960.1
1960.10
1960.104
1960.105
1960.105_det.jpg
1960.11
1960.12
1960.12_det.jpg
1960.13
1960.13_det.jpg
1960.16.a-b
1960.3
1960.4
1960.4_det.jpg
1960.45.a-b
1960.55
1960.58
1960.58_det.jpg
1960.59
1960.59_det1.jpg
1960.59_det2.jpg
1960.60
1960.68
1960.7
1960.7_det1.jpg
1960.7_det2.jpg
1960.7_det1.jpeg
1960.7_det2.jpeg
1960.78

Wish I could

[Moo the Dog]

Ahh, well, that is a bit more complicated... your example didn't show that kind of stuff.

[FDibbins]

not sure if this is what you are looking for, but gibe it a try, copied down...

=IF(ISERROR(SEARCH("_",H1,1)),H1,LEFT(H1,SEARCH("_",H1,1)-1))

then in the next column over, use this, copied down

=IF(COUNTIF($I$1:$I$21,I1)<2,"",I1)
(adjust ranges as needed)

[Isara]

The first formula worked like a charm! Thank you!!!

[FDibbins]

i thought 1 of your requirements was to remove duplicates? the 2nd formula would do that.

either way, you'r welcome, and consider adding the reputation to those that helped (see points 2 & 3 below)