Remove Spaces

[Alex O]

Is there a way the formula below can be edited to remove all spaces that follow a comma (ie T3 should be BD Cont # 3738527,3908669,3908664,3908665, etc.)?

Thanks

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 506px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>T</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Calibri">BD Cont # 3738527, 3908663, 3908664, 3908665, 4133283</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Calibri">BD Cont # 3625533, 3625550</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Calibri">BD Cont # 4071213</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>T3</TD><TD>=IF(B3="","",TRIM(LEFT(TRIM(VLOOKUP(B3,'Step 2 Output & Scrub Sheet'!$AO$4:$AP$500,2,FALSE)),9)&" "&SUBSTITUTE(VLOOKUP(B3,'Step 2 Output & Scrub Sheet'!$AO$4:$AP$500,2,FALSE),LEFT(TRIM(VLOOKUP(B3,'Step 2 Output & Scrub Sheet'!$AO$4:$AP$500,2,FALSE)),9)," ")))</TD></TR><TR><TD>T4</TD><TD>=IF(B4="","",TRIM(LEFT(TRIM(VLOOKUP(B4,'Step 2 Output & Scrub Sheet'!$AO$4:$AP$500,2,FALSE)),9)&" "&SUBSTITUTE(VLOOKUP(B4,'Step 2 Output & Scrub Sheet'!$AO$4:$AP$500,2,FALSE),LEFT(TRIM(VLOOKUP(B4,'Step 2 Output & Scrub Sheet'!$AO$4:$AP$500,2,FALSE)),9)," ")))</TD></TR><TR><TD>T5</TD><TD>=IF(B5="","",TRIM(LEFT(TRIM(VLOOKUP(B5,'Step 2 Output & Scrub Sheet'!$AO$4:$AP$500,2,FALSE)),9)&" "&SUBSTITUTE(VLOOKUP(B5,'Step 2 Output & Scrub Sheet'!$AO$4:$AP$500,2,FALSE),LEFT(TRIM(VLOOKUP(B5,'Step 2 Output & Scrub Sheet'!$AO$4:$AP$500,2,FALSE)),9)," ")))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[jasonb75]

Try the principle of

=SUBSTITUTE(your formula,", ",",")

 

[jasonb75]

Which cleaned up a little, makes something like

=IF(B3="","",TRIM(LEFT(TRIM(VLOOKUP(B3,'Step 2 Output & Scrub Sheet'!$AO$4:$AP$500,2,FALSE)),9)&" "&MID(TRIM(SUBSTITUTE(VLOOKUP(B3,'Step 2 Output & Scrub Sheet'!$AO$4:$AP$500,2,FALSE),", ",",")),10,255)))

 

[Alex O]

Jason,
I appreciate the prompt suggestion, but the formula was written to display "BD Cont #" before the first value only. This negates that portion of the formula.....any other thoughts?

 

[jasonb75]

Sorry Alex, I misread the original formula slightly, although my original suggestion will still work. i.e.

=SUBSTITUTE(IF(B3="","",TRIM(LEFT(TRIM(VLOOKUP(B3,'Step 2 Output & Scrub Sheet'!$AO$4:$AP$500,2,FALSE)),9)&" "&SUBSTITUTE(VLOOKUP(B3,'Step 2 Output & Scrub Sheet'!$AO$4:$AP$500,2,FALSE),LEFT(TRIM(VLOOKUP(B3,'Step 2 Output & Scrub Sheet'!$AO$4:$AP$500,2,FALSE)),9)," "))),", ",",")<!-- / message -->

 

[Alex O]

Works Perfect!! Thanks