Remove the fourth comma

[pagrender]

Hi all,

Can someone write a formula that can remove the fourth comma from a text string? There's a catch - if there are only four commas, we need to leave all the commas. Basically, I'd like to remove the comma between Schulte and Karen, Rothfelder and Lashell, Keep Woodman's the same, and remove the comma between Garner and Darci.


Here is my sample data, in cells A1:A4
<TABLE style="WIDTH: 332pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=443 border=0><COLGROUP><COL style="WIDTH: 332pt; mso-width-source: userset; mso-width-alt: 16201" width=443><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 332pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=443 height=20>951301,02/24/2009,22.38,SCHULTE, KAREN,</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>952279,03/24/2009,191.40,ROTHFELDER, LASHELL ANN MARIE,</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>958381,10/07/2009,100.00,WOODMAN'S,</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>958833,10/13/2009,39.95,GARNER, DARCI,

Thanks!
Pete
</TD></TR></TBODY></TABLE>

 

[VoG]

Possibly (depending on how many commas there may be)

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))=5,SUBSTITUTE(A1,",","",5),A1)

 

[pagrender]

That worked!

Actually, it took away the fifth comma, but a small tweak to the formula made it go.

Thanks!