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)))),"")
 

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
695
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
Joined
Feb 3, 2018
Messages
12
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
Joined
Oct 7, 2013
Messages
695
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.
 

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
695
Older Excel Learner, Good afternoon.

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

Have a nice day!
 

Forum statistics

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

Some videos you may like

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