some MS Excel help

[kimchicowboy]

hey everybody. been pretty busy. hope everyone is doing well. but i can use some help regarding some data.

in an excel spreadsheet, i have a bunch of data like:

XM_287097.1
XM_283717.1
XM_283707.1

now what i need help with is how to get rid of that last decimal place. i can't seem to do it the normal way because of the letters at the beginning. i refuse to do it one-by-one because i have over 10,000 of these entries.

thanks for any help!

[coleslaw]

I'm assuming that the format for these cells is "text", so you could then use the LEFT function, specifying the number of characters in that string that you want returned.

e.g. If these strings are in column B and in rows 1 - 10,000, you could create a separate column and then use the left function as follows:

=LEFT($B1,9)

Copy this forumula to the remaining 9,999 rows and then "Hide" the original column if needed.

:)

[ramazank2]

That is realy good to know. Thanks

[kimchicowboy]

thanks a lot coleslaw! a new scientific breakthrough may come out of this. hehe.

[ski]

Now if only someone could help me in my Bluetooth thread!!

http://www.gotapex.com/forums/showthread.php?threadid=65855

:D

[RoniMan]

i was going to say to split up your entries into two columns, one with the letters and the underscore, and one with the numbers (then you could format the way you wanted to previously), but coleslaws way is probably much easier.
:thumb:

[coleslaw]

Originally posted by kimchicowboy
thanks a lot coleslaw! a new scientific breakthrough may come out of this. hehe.

I get 5% royalties. :P

I'm an Excel master because I use it basically every day. My favorite function is VLOOKUP and my life would be less exciting without pivot tables. :cool:

[eSDee]

The hack way to do it is go to Find->Replace, put .1 in the Find and leave the Replace field empty. Like I said, it's a a hack.

[CluelessSi]

That way would work is we assume that all the fields are the same size =) and does this affect calculations at all? meaning will the .1 be calculated in even though it is not shown?

[eSDee]

Originally posted by CluelessSi
That way would work is we assume that all the fields are the same size =) and does this affect calculations at all? meaning will the .1 be calculated in even though it is not shown?

The Hack version does nothing but do what it is told to do, which is get rid of the .1. The data might be ususable after the Hack is used, but that's not the Hack's worry. The Hack only cares about number 1....




;)