some MS Excel help

kimchicowboy · 09-10-2003, 10:25 AM

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 · 09-10-2003, 10:32 AM

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 · 09-10-2003, 10:35 AM

That is realy good to know. Thanks

kimchicowboy · 09-10-2003, 10:42 AM

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

ski · 09-10-2003, 10:44 AM

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

http://www.gotapex.com/forums/showth...threadid=65855 (Wanna help me with my Bluetooth project at work?)

RoniMan · 09-10-2003, 10:46 AM

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.

coleslaw · 09-10-2003, 11:06 AM

Quote:

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

I get 5% royalties.

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.

eSDee · 09-10-2003, 12:04 PM

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 · 09-10-2003, 01:22 PM

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 · 09-10-2003, 01:38 PM

Quote:

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....

09-10-2003, 10:25 AM	#1
kimchicowboy Admiral Join Date: May 2000 Location: in the middle of the pacific ocean Posts: 5,344	some MS Excel help 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!

09-10-2003, 10:32 AM	#2
coleslaw Arrrhh! Join Date: May 2000 Location: LI NY Posts: 6,807	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. __________________ A priest, a paladin and Varimathras walk into a bar...

09-10-2003, 12:04 PM	#8
eSDee Old Skooler Numba 1 Join Date: Nov 2000 Location: Diego Posts: 10,063	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. __________________ ~~~~~~~~~~~~ 3 days ~ Willie Nelson 3 days I dread to see arrive 3 days I hate to be alive 3 days filled with tears and sorrow yesterday today and tomorrow

09-10-2003, 01:22 PM	#9
CluelessSi Rear Admiral Lower Half Join Date: Jun 2000 Posts: 2,038	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? __________________ ~Reality is an illusion~ 1 Thess 5:16-18

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

09-10-2003, 10:35 AM	#3
ramazank2 Commander Join Date: Jun 2000 Location: Costa Mesa, CA Posts: 1,295	That is realy good to know. Thanks

09-10-2003, 10:42 AM	#4
kimchicowboy Admiral Join Date: May 2000 Location: in the middle of the pacific ocean Posts: 5,344	thanks a lot coleslaw! a new scientific breakthrough may come out of this. hehe.

09-10-2003, 10:44 AM	#5
ski Admiral Join Date: Mar 2001 Location: Utah Posts: 5,420	Now if only someone could help me in my Bluetooth thread!! http://www.gotapex.com/forums/showth...threadid=65855 (Wanna help me with my Bluetooth project at work?)

09-10-2003, 10:46 AM	#6
RoniMan Admiral Join Date: Apr 2000 Location: Temple City, CA Posts: 5,140	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.

User Name
Password
Remember Me?