# Lose to left of decimal

#### bam12

##### Well-known Member
How do I lose anything to left of decimal and show figure as whole number

0811294.12345. Would be 12345

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
How do I lose anything to left of decimal and show figure as whole number

0811294.12345. Would be 12345
Try this...

=--MID(A2,FIND(".",A2)+1,15)

Small problem. I need 5 after decimal and can end in zero

Small problem. I need 5 after decimal and can end in zero

How about posting SEVERAL examples with the desired results.

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

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
The formula I suggested does that.

Please post some examples so we know what results you expect.

I must have did something wrong on the zero one it truncated zero and all after that were in to many digits. Maybe I have formatted wrong

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.

both truncate the ending zero
everything is okay but the ones ending in zero

811294.12344 12344
811294.12346 12346
811294.12348 12348
811294.12350 1235
811294.12352 12352
811294.12354 12354
811294.12356 12356
811294.12358 12358
2711294.12457 12457

here is sample of what i am getting

Replies
3
Views
513
Replies
16
Views
177
Replies
2
Views
149
Replies
8
Views
141
Replies
4
Views
99

1,203,101
Messages
6,053,530
Members
444,670
Latest member
laurenmjones1111

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back