# Formula to extract number after dot in a cell

#### Older Excel Learner

##### New Member
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)))),"")

#### Marcílio_Lobão

##### Well-known Member
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.

#### Older Excel Learner

##### New Member
Thanks Marcilio for replying, It takes into account other things.

Could you just amend my original formula if ossible.

Thanks.

#### Marcílio_Lobão

##### Well-known Member
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.

#### Older Excel Learner

##### New Member
Thank You So Much. I have sent you a PM by way of explanation. Thanks again, greatly appreciated.

#### Marcílio_Lobão

##### Well-known Member
Older Excel Learner, Good afternoon.

Have a nice day!

1,081,415
Messages
5,358,533
Members
400,502
Latest member
price83

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...