Spaces at the end of data

[sjs]

I'm trying to run a vlookup but the search array has some cells that contain
data in which there is a space at the end. Is there an easy way to remove
the end space when only some of the cells have the space?

tks,
steve

 

[T. Valko]

Download and install this macro:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Then select the range of cells in question.
Run the macro

 

[sjs]

Biff, not sure installing a macro is safe, virus wise. Any other way to
remove the space?

tks,
steve

 

[T. Valko]

I can assure you that the macro is safe and comes from a very reputable
person. I use this macro 10's of times every day.

If you don't want to use it then about all I can think of is using a helper
column with a formula that strips off the trailing space character.

Assume the range in question is A1:A10...

Enter this formula in B1 and copy down to B10:

=TRIM(A1)

Note that will only remove char 32 standard space characters.

 

[Jacob Skaria]

data in which there is a space at the end.
If you are sure there is only one space you can try out the below..

With data in Col A/B and lookup value in C1

=IF(ISNA(VLOOKUP(C1,A:B,2,0)),IF(ISNA(VLOOKUP(C1&"
",A:B,2,0)),"",VLOOKUP(C1&" ",A:B,2,0)),VLOOKUP(C1,A:B,2,0))

Depending on your data you can try the below formula using MATCH() and
INDEX(). Please note that the below will lookout for the first entry in the
list which match the lookup value string..
=INDEX(B:B,MATCH(C1&"*",A:A,0))


If this post helps click Yes

 

[Ron Rosenfeld]

I'm trying to run a vlookup but the search array has some cells that contain
data in which there is a space at the end. Is there an easy way to remove
the end space when only some of the cells have the space?

tks,
steve

Set up a helper column.

Original Data
A1:

If you don't mind also removing spaces at the beginning; and replacing multiple
consecutive spaces within the string with a single space:

B1: =TRIM(A1)

If you only want to remove the terminal space:

B1: =IF(RIGHT(A1,1) = " ",LEFT(A1,LEN(A1)-1))

If your data came from an HTML document, try:

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

(or use the Find/Replace tool to replace the nbsp with nothing. To enter that
into the Find bar, while holding down the <alt> key, type (sequentially) 0160
on the NUMERIC KEYPAD (not on the numbers above the keyboard). Then release
the <alt> key.
--ron