formula to get value between last two comma

[skywalker001]

hello all,

I need a formula to extract value between last two comma in a string

eg. aaa,bb_1,ccc,ddd,v

output: ddd

 

[snipescc]

Simplest thing to do may be to copy the data onto another sheet, then use text to columns with commas as the delimeter. Are there the same number of commas in every line?

 

[skywalker001]

No the # of comma will vary. I am open for vba solutions as well.

 

[snipescc]

How consistent will the number of characters at the end be? Could you use the RIGHT formula to chop off the relevant data?
Another thought would be using text to columns, then using nested IF statements to check if a cell is blank. Start at the far right (a bit beyond however many columns you end up with), use ISBLANK to check if a cell is blank. if it is, check again one to the left. Once it isn't, grab the cell to the left of that one to get the data in the second to last cell.

 

[skywalker001]

no I'm afraid we cant use right function to chop since the number of char at the end will be random.

this thread gives solution which is almost I am looking for https://www.mrexcel.com/forum/excel-questions/457582-copy-text-after-last-comma-cell.html

 

[bosco_yip]

Try,

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,",",REPT(" ",30)),60),30))

Regards
Bosco

 

[skywalker001]

Beautiful...cheers mate!!!