Remove extra space

[klynshoe]

Hello,

I have a large data set where there is an extra space at the end of every line causing my vlookups to not work. You can't see them in the data listed below, but is there any easy way to remove the spaces only at the end of each line?

<TABLE style="WIDTH: 219pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=292 border=0><COLGROUP><COL style="WIDTH: 219pt; mso-width-source: userset; mso-width-alt: 10678" width=292><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 219pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=292 height=20>GM-GME_GPF-Managers</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>GM-GME_GPF-Managers</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>GM-GME_GPF-Prime Client Mgmt</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>GM-GME_GPF-Prime Client Mgmt</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>GM-GME_GPF-Prime Client Mgmt</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>GM-GME_GPF-Prime Client Mgmt</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>GM-GME_GPF-Prime Client Mgmt</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>GM-GME_GPF-Prime Client Mgmt 2</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>GM-GME_GPF-Prime Client Mgmt 2</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>GM-GME_GPF-Prime Client Mgmt 2</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>GM-GME_GPF-Prime Client Mgmt 2</TD></TR></TBODY></TABLE>

USING EXCEL 2007

 

[Trevor G]

Look to use the Trim formula

 

[MrKowz]

Highlight that column, use Text-To-Columns, click Finish.

 

[Trevor G]

If you use that method in the Text To Column make sure you select the Next button at the bottom and select the Delimeters space option then click finish

 

[MrKowz]

If you use that method in the Text To Column make sure you select the Next button at the bottom and select the Delimeters space option then click finish

Actually, don't need to do that.

Text-to-columns will trim off leading and trailing spaces automatically.

 

[Trevor G]

I tried that but it didn't remove the spaces in 2007. so I went the extra steps, as long as it works I would be prepared to do the extra steps.

I do agree that it should do it straight away.....

 

[MrKowz]

I tried that but it didn't remove the spaces in 2007. so I went the extra steps, as long as it works I would be prepared to do the extra steps.

I do agree that it should do it straight away.....

Gotcha

Strange that it drops the spaces in 2003, and not in 2007.

 

[klynshoe]

Would you please clarify the extra steps?

I highlighted the entire column, went to Text to Columns, clicked Next, selected "Treat consecutive delimiters as one" and clicked Finish. Nothing happened.

USING EXCEL 2007

 

[Trevor G]

As you go through each step:

Select Column
Select the Data Tab
Click the Text to Columns feature
Click Next as Delimited is first step
Next select Space remove tick from Tab
Click Next or Finish
If Clicked Next then Click Finish

 

[klynshoe]

This doesn't do anything. Should anything be selected on step 2 where the delimiters are listed?

USING EXCEL 2007