-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Lose to left of decimal
- Thread starter bam12
- Start date
Sorry about confusion. Let me explain the number sequence.
0811294.12345.
08 is region
11294 is julian date
12345 is actually a counter so has things added it increases
we need to use the counter part for a report so need all 5 digits
So if we get to 12350. Need to show it that way
TIA
0811294.12345.
08 is region
11294 is julian date
12345 is actually a counter so has things added it increases
we need to use the counter part for a report so need all 5 digits
So if we get to 12350. Need to show it that way
TIA
Upvote
0
energman58
Well-known Member
- Joined
- Oct 25, 2010
- Messages
- 553
You need to format your original reference as text not a number - otherwise any formula like T.Valko's will truncate the string so something that displays like 123456.12340 will return 1234 because even if your number format is to show 5 dp in the original cell Excel only carries the significant figures internally.
A supplementary thought is that surely if the part after the decimal point is a sequence number then
123456.999
should be followed by
12456.1000
and you need to pad the front of your number with zeros otherwise you will get 999 turned into 9990 which is followed by 1000!
So I think you have to do the following:
Turn all your data into text
Drop the bit to the left of the DP using a formula like this
=(--MID(B9,FIND(".",B9)+1,LEN(B9)-FIND(".",B9)+1))
Excel will generally interpret this as a number (in 2007 and later at the least) but I think some earlier versions of Excel might need this wrapped in a VALUE function to turn the text into numbers - you need to check if you use an older version.
If I am right and 999 should display as 00999 then you can force this to happen by using a custom number format with 00000 as the code in the format field.
A supplementary thought is that surely if the part after the decimal point is a sequence number then
123456.999
should be followed by
12456.1000
and you need to pad the front of your number with zeros otherwise you will get 999 turned into 9990 which is followed by 1000!
So I think you have to do the following:
Turn all your data into text
Drop the bit to the left of the DP using a formula like this
=(--MID(B9,FIND(".",B9)+1,LEN(B9)-FIND(".",B9)+1))
Excel will generally interpret this as a number (in 2007 and later at the least) but I think some earlier versions of Excel might need this wrapped in a VALUE function to turn the text into numbers - you need to check if you use an older version.
If I am right and 999 should display as 00999 then you can force this to happen by using a custom number format with 00000 as the code in the format field.
Upvote
0
