• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

copy text before the last 3 commas in excel

Alina_T

New Member
Hello! please help me with a formula that copy the text before the last 3 commas in excel. The reason is that I have in a cell all the info separated with comma for each month, but I need only the last 3 month. The cells are not the same, could be 7 commas, 12 commas, 18 commas

6/ianuarie 2019, 6/februarie 2019, 6/martie 2019, 5/aprilie 2019, 5/mai 2019, 4/iunie 2019, 3/iulie 2019, 3/august 2019, 3/septembrie 2019, 3/octombrie 2019, 2/noiembrie 2019, 2/decembrie 2019, 2/ianuarie 2020, 2/februarie 2020, 2/martie 2020, 2/aprilie 2020, 2/mai 2020, 2/iunie 2020, 2/iulie 2020, 2/august 2020, 2/septembrie 2020, 2/octombrie 2020, 2/noiembrie 2020, 2/decembrie 2020, 2/ianuarie 2021, 2/februarie 2021, 2/martie 2021, 2/aprilie 2021

or
24/iunie 2020, 27/iulie 2020, 29 august 2020, 32/septembrie 2020, 36/octombrie 2020, 37/noiembrie 2020, 37/decembrie 2020, 36/ianuarie 2021, 36/februarie 2021, 34/martie 2021, 36/aprilie 2021

or
1/octombrie 2020, 1/noiembrie 2020, 1/decembrie 2020, 1/ianuarie 2021, 1/februarie 2021, 1/martie 2021, 1/aprilie 2021

Thank you!
 

Alina_T

New Member
you are awesome! thank a lot!! seems to be the right thing for me! but can you explain a little bit? if I want 4 commas what I have to change?
 

shrivallabha

Excel Ninja
Here's longer version of the formula:
=TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",LEN(A2)),LEN(A2)-LEN(SUBSTITUTE(A2,",",""))-2),LEN(A2)))
where term in red (2) is correlated to number of terms needed from right (3) i.e. n-1
If you need 4 terms then you just need to change it to 3, for 5 it will be 4.

Here we are using functionality of SUBSTITUTE's 4th parameter of specifying the instance to replace.
 

Alina_T

New Member
Here's longer version of the formula:
=TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",LEN(A2)),LEN(A2)-LEN(SUBSTITUTE(A2,",",""))-2),LEN(A2)))
where term in red (2) is correlated to number of terms needed from right (3) i.e. n-1
If you need 4 terms then you just need to change it to 3, for 5 it will be 4.

Here we are using functionality of SUBSTITUTE's 4th parameter of specifying the instance to replace.
thank you!!!
 
Top