Formula to extract number after dot in a cell

Older Excel Learner

New Member
Joined
Feb 3, 2018
Messages
12
The formula below extracts the first of two different numbers in a single cell that are seperated by a . (dot, full stop, period)

So if I have 20.4 in a cell , it will extract any number to the left of the dot, in this example 20

Can somebody alter the formula to extract any number to the right of the dot, in this example 4

The formula is perfect for me I just don't know what to change within it for it to select right of dot instead of left of dot

Thanks.

=IF(COUNTIF(S10:S10,"?*"),SUMPRODUCT(0+(0&TRIM(MID(SUBSTITUTE(S10:S10,".",REPT(" ",99)),1,99)))),"")
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Older Excel Learner, Good morning.

I'm really curious to know why you had to use this formula to get the left part of your information.

Try to use these formulas:

Left cell part:
=LEFT(S10, SEARCH(".", S10)-1)

Right cell part:
=MID(S10, SEARCH(".",S10)+1, 100)

Please, tell us if it worked as you desire.
I hope it helps.
 
Upvote 0
Older Excel Learner,

"...Could you just amend my original formula if ossible...."
Ok. No more comments. We are here to help.

Just change the indicator 1 to 99.

Before:
=IF(COUNTIF(S10:S10,"?*"),SUMPRODUCT(0+(0&TRIM(MID(SUBSTITUTE(S10:S10,".",REPT(" ",99)),1,99)))),"")

Now:
=IF(COUNTIF(S10:S10,"?*"),SUMPRODUCT(0+(0&TRIM(MID(SUBSTITUTE(S10:S10,".",REPT(" ",99)),99,99)))),"")

I hope it helps.
 
Upvote 0
Older Excel Learner, Good afternoon.

I'm glad to have helped you with your doubt.

Have a nice day!
 
Upvote 0

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top