- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel Help - need to isolate the data after the decimal point (fractional?)
Posted on 3/28/15 at 9:03 am
Posted on 3/28/15 at 9:03 am
I have a column of numbers that always has 6 digits, then a decimal point, them a series of numbers 4 to 8 characters after the decimal point. I need to isolate just the data after the decimal point ( this could occasionally include letters and not just numbers) can I use excel to do this?
Ex 1: 404010.123456 ( I need to isolate just the 123456)
Ex 2: 607160. 12345C78. ( I need to isolate just the 12345C78)
I tried to use the "truncate" (TRUNC) formula in Excel but i can only get it to isolate the integer (whole number before decimal place)
Ant help greatly appreciated.
Ex 1: 404010.123456 ( I need to isolate just the 123456)
Ex 2: 607160. 12345C78. ( I need to isolate just the 12345C78)
I tried to use the "truncate" (TRUNC) formula in Excel but i can only get it to isolate the integer (whole number before decimal place)
Ant help greatly appreciated.
This post was edited on 3/28/15 at 9:05 am
5
Posted on 3/28/15 at 9:13 am to Crow Pie
Assuming data is in column A, in B1 use:
=right(A1,len(A1)-7)
=right(A1,len(A1)-7)
This post was edited on 3/28/15 at 9:16 am
Posted on 3/28/15 at 9:25 am to aaronb023
quote:
Assuming data is in column A, in B1 use:
=right(A1,len(A1)-7)
ok will try ...what is the "len"
function?
ETA Yes, it works!!! It seems that it reporting anything starting with the 7th number or character in the string.
This post was edited on 3/28/15 at 9:32 am
Posted on 3/28/15 at 9:42 am to Crow Pie
Len outputs the number of characters in a string.
Posted on 3/28/15 at 9:44 am to Crow Pie
If always after the decimal, and only one is present, can use FIND() also. This will eliminate the 7th digit restriction.
Posted on 3/28/15 at 9:52 am to Crow Pie
Highlight column.
Click on data
Click on text to column
Click on fixed width, click next
Then you can choose where to break up the column in 2. And click either next or finish.
If you have multiple columns and it's in column A, insert a column so column B will be blank, and it'll separate the stuff after the decimal and put it in column B.
Click on data
Click on text to column
Click on fixed width, click next
Then you can choose where to break up the column in 2. And click either next or finish.
If you have multiple columns and it's in column A, insert a column so column B will be blank, and it'll separate the stuff after the decimal and put it in column B.
Posted on 3/28/15 at 10:03 am to Crow Pie
Text to columns. Set the limit as the decimal
Posted on 3/28/15 at 12:05 pm to GFaceKillah
Thanks to all...this will help to quickly break off/filter specific project cost data (labor/rental equipment/supplies) that was previously a manual process.
Posted on 3/28/15 at 3:08 pm to Crow Pie
Here's another way, assuming the number is in cell A1:
=A1 - int(A1)
=A1 - int(A1)
Posted on 3/28/15 at 6:54 pm to foshizzle
quote:
Here's another way, assuming the number is in cell A1:
=A1 - int(A1)
quote:
Ex 2: 607160. 12345C78. ( I need to isolate just the 12345C78)
That's not a decimal number. Also, it doesn't work for negative numbers like -2.8. You should use TRUNC instead of INT.
Posted on 3/28/15 at 10:01 pm to Spock's Eyebrow
quote:
That's not a decimal number.
True, but then none of the other proposed solutions would work either without throwing in a TRIM().
quote:
it doesn't work for negative numbers like -2.8
Also true, but he indicated he's working with project cost figures, they probably aren't negative.
Posted on 3/29/15 at 8:26 am to Crow Pie
if you have the PowerQuery add-in, you can load it as a table then separate the column by the ".".
By the way, for those not familiar with PowerQuery, you're really missing out on an incredibly functional extension of Excel. I've used it to replace all of our Access model at work.
By the way, for those not familiar with PowerQuery, you're really missing out on an incredibly functional extension of Excel. I've used it to replace all of our Access model at work.
Posted on 3/30/15 at 7:27 am to LSUtigerME
quote:
If always after the decimal, and only one is present, can use FIND() also. This will eliminate the 7th digit restriction.
I would do it using FIND, as well. Something like: =MID(A1,FIND(".",A1)+1,99)
Posted on 3/30/15 at 7:46 am to HurricaneDunc
quote:
if you have the PowerQuery add-in, you can load it as a table then separate the column by the ".".
Come to think of it, you can use Excel's Text to Columns Wizard to do the same thing.
Page 1 of 1
Back to top
Follow TigerDroppings for LSU Football News