• 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

A

Alina_T

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!
 
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?
 
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.
 
shrivallabha said:
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.
Click to expand...
thank you!!!
 
You must log in or register to reply here.
Back
Top