keeping everything after the last comma

[brianflieck]

Hello, i have a problem with dates in cells. I have data, that doubles and triples the date/time in a cell as the recordds build up and i only need the last date/time in any of these cells which comes after the last comma.

for example.

2016-04-11 15:16:27.75,2016-04-11 18:01:39.357,2016-04-12 10:32:30.74

2016-04-11 15:16:39.087,2016-04-12 10:32:33.62 i'd only want to keep all the data in read, and format it to date so it only gives me back the date. Sometimes, its 2 dates/time or 3 or more. thanks!

 

[Alan_P]

Hi Brian,

Give this formula a try;

=DATEVALUE(RIGHT(A1,LEN(A1) - FIND(CHAR(1),SUBSTITUTE(A1,",",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))))

Hope this helps,
Cheers,
Alan.

 

[brianflieck]

Thanks Alan! one follow up, is there a way to apply the formula so that when there's only one date in the column it doesn't mess it up? right now if i apply the formula to when there is only one date and comma it breaks.

thanks so much!

2016-04-11 15:16:27.75,2016-04-11 18:01:39.357,2016-04-12 10:32:30.74

2016-04-11 15:16:39.087,2016-04-12 10:32:33.62 2016-04-11 15:16:39.087

<tbody>
</tbody>

 

[Alan_P]

Hi Brian,

So are you saying it is possible to have a value like "2016-04-11 15:16:39.087," with a random comma at the end?

Or will it just be "2016-04-11 15:16:39.087" i.e. no comma at the end?

If it's the latter (no comma):

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))>=1,DATEVALUE(RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,",",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))),DATEVALUE(A1))

If it's the former (comma): I'm all out of ideas! Sorry

Cheers,
Alan.

 

[brianflieck]

Hi Alan, i was hoping you could give me some magic again with this comma issue i'm having. i have seprate colum with names, that have comma's. same issue as above, i only want to keep the single names, and the name after the last comma. is that doable?

thanks in advance for being a lifesaver!

Brian Flieck

Carl Milbourne

Carl Milbourne,Carl Milbourne

Carl Milbourne,Carl Milbourne

Carl Milbourne,Carl Milbourne

Carl Milbourne

Brian Flieck

Carl Milbourne,Carl Milbourne

Carl Milbourne

Carl Milbourne

Carl Milbourne

Carl Milbourne

Carl Milbourne

Brian Flieck

Brian Flieck

Brian Flieck,Brian Flieck

Brian Flieck

Brian Flieck,Brian Flieck

Carl Milbourne

Carl Milbourne

Carl Milbourne

Carl Milbourne

Carl Milbourne

Carl Milbourne

Carl Milbourne

Carl Milbourne

Carl Milbourne

Carl Milbourne

Brian Flieck

Brian Flieck

Brian Flieck

Brian Flieck

Brian Flieck

Brian Flieck

Brian Flieck,Brian Flieck

Brian Flieck,Brian Flieck

Brian Flieck,Brian Flieck

Brian Flieck,Brian Flieck

Brian Flieck,Brian Flieck

<colgroup><col></colgroup><tbody>
</tbody>

 

[brianflieck]

so i found this, which only works on the first , if there are more then 2 names, it only cuts off one of the comma's.

=RIGHT(A1,LEN(A1)-FIND(",",A1))


Brian Flieck,Brian Flieck,Brian Flieck
Nicolai Pinguel,Brian Flieck,Brian Flieck
Greg Leddy,Greg Leddy,Greg Leddy
Greg Leddy,Greg Leddy,Greg Leddy,Greg Leddy
Brian Flieck,Brian Flieck,Brian Flieck,Brian Flieck,Brian Flieck
Brian Flieck,Brian Flieck,Brian Flieck




 

[Rick Rothstein]

so i found this, which only works on the first , if there are more then 2 names, it only cuts off one of the comma's.

=RIGHT(A1,LEN(A1)-FIND(",",A1))

Brian Flieck,Brian Flieck,Brian Flieck
Nicolai Pinguel,Brian Flieck,Brian Flieck
Greg Leddy,Greg Leddy,Greg Leddy
Greg Leddy,Greg Leddy,Greg Leddy,Greg Leddy
Brian Flieck,Brian Flieck,Brian Flieck,Brian Flieck,Brian Flieck
Brian Flieck,Brian Flieck,Brian Flieck


Give this formula a try...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99),99))

 

[brianflieck]

Give this formula a try...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99),99))

Rick that worked! thank you greatly.