get data from right after last comma

  • Hi!


    My data in Col. A looks like this:


    1,2,3,432
    1,2,3,4
    1,2,3,43


    I wish to get data after the last comma in each row (my data does not have any spaces). So my result would look like this:


    432
    4
    43


    I have 2 formula which I feel should do the work:


    1. =RIGHT(A1,(FIND(",",A1))) --> However, this is only giving the last 2 characters in the result (including comma). Any advise to fine tune it?


    2. =TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99)) --> works nice and gives exact result. However, this was designed for extracting data from string with spaces, so not sure what trim and substitute would be doing here. Any advise on how to make it simpler/ reduce its length by chopping off the un-necessary code?


    Regards,


    Naira

  • Re: get data from right after last comma


    Hi


    You cannot remove parts from this formula.
    It works as such, adding spaces and triming the result.
    Whether you have spaces in your cells or not does not matter.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: get data from right after last comma


    It's unlikely you'll be able to reduce the length of your working formula, and why would you want/need to? You'll definitely need to use SUBSTITUTE, and TRIM is used because each comma is being replaced with 99 spaces, which TRIM then removes.

  • Re: get data from right after last comma


    Thanx for the replies guys.


    Why I would want to reduce the size of the formula is because I am running about 2.5 million calculations on a single workbook and my work sheet is being updated by real-time sub-second data, so I try to squeeze out anything that's not useful.


    Any ideas why the first formula isn't working? May be someone could explain what is wrong with the logic of the first one?

  • Re: get data from right after last comma


    Quote from naira;709787

    Any ideas why the first formula isn't working? May be someone could explain what is wrong with the logic of the first one?


    Simple. FIND(",",A1) will find the comma from LEFT to RIGHT. Not the other way round.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: get data from right after last comma


    Use this formula but you would need to use "," on one of the cell to execute this one.


    =TRIM(MID(A1,FIND(CHAR(1), SUBSTITUTE(A1,$F$1,CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,$F$1,""))))+1,LEN(A1)))

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!