Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spaces at the end of data
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spaces at the end of data
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 -- Biff Microsoft Excel MVP "sjs" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spaces at the end of data
Biff, not sure installing a macro is safe, virus wise. Any other way to
remove the space? tks, steve "T. Valko" wrote: 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 -- Biff Microsoft Excel MVP "sjs" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spaces at the end of data
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. -- Biff Microsoft Excel MVP "sjs" wrote in message ... Biff, not sure installing a macro is safe, virus wise. Any other way to remove the space? tks, steve "T. Valko" wrote: 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 -- Biff Microsoft Excel MVP "sjs" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spaces at the end of data
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 --------------- Jacob Skaria "sjs" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spaces at the end of data
On Tue, 15 Sep 2009 18:08:02 -0700, sjs wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Splitting up data by spaces | Excel Discussion (Misc queries) | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
is there a quick way to put spaces in data? | Excel Discussion (Misc queries) | |||
Spaces in Data | New Users to Excel | |||
Spaces in Data | Excel Discussion (Misc queries) |