Removing all spaces...

[cps3488]

<TABLE style="WIDTH: 95pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=126><COLGROUP><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 height=17 width=126>

04NMI00225SL 10g18
</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 height=17>04NMI00225SL3 4a16</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 height=17>04NMI00 230a 17</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 height=17>04 NMI 00230a 17</TD></TR></TBODY></TABLE>

I have a list of data that contains both numbers and letters. Is there a formula that will remove all spaces? I know the clean function can get me down to one space but I want to remove all spaces so my list looks like this...

<TABLE style="WIDTH: 95pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=126><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 height=17 width=126>04NMI00225SL10g18
</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 height=17>04NMI00225SL34a16</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 height=17>04NMI00230a17</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 height=17>04NMI00230a17</TD></TR></TBODY></TABLE>

 

[VoG]

Try

=SUBSTITUTE(A1," ","")

 

[cps3488]

I have tried that but it does not seem to work for me. Not sure why??

 

[VoG]

Maybe (especially if you imported these values from the internet)

=SUBSTITUTE(A1,CHAR(160),"")

 

[Rick Rothstein]

I have tried that but it does not seem to work for me. Not sure why??

Maybe you got your data from the web and it contains non-breaking spaces (ASCII 160) perhaps along with real spaces? Try this...

=SUBSTITUTE(SUBSTITUTE(A1," ",""),CHAR(160),"")

 

[cps3488]

Problem solved!!

Thanks VoG & Rick Rothstein!!!