Cannot get rid of leading space in a cell

[jrobin5881]

I receive an excel sheet of data from another application. This sheet is saved in a folder. I have VBA written to copy and paste the sheet into another workbook. All of that works fine. Here's the issue: Column A of the imported sheet has zipcodes that have a leading space before the number. I need to use that column as a lookup for the name of the town that I have on a hidden worksheet(I use the index/match function). I need to get rid of the leading blank space in order for the lookup function to work and I can't. I've tried everything:

-Added a column and tried trim function
-Put a 1 in a blank cell and tried Copy Paste Special Multiply
-Format Cells - Text on the column
-If I use the F2,Home,Delete,Enter keys in this sequence it works. I was going to put that in a loop and send it down the column but I couldn't get the send keys function to work.
-I tried using the paintbrush formatting tool from the lookup sheet to the data sheet.

I don't know what else to do.

 

[SkipVought]

Hi,

Have you tried Edit > Replace?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

You could, of course, prepend a SPACE to your lookup value.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

You could also use the Find method.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jrobin5881]

Skip Thanks -
I tried prepending a space to the lookup table by using =" "&B2 which did the trick. BUT, the index/match would not find the value in the lookup. So, I tried the Find Replace method and could not get that to work. In the find box I hit the space bar and clicked replace, no good. Then I put " " in the find box and hit replace again no good.
Driving me nuts this is...

 

[WinblowsME]

How about using the right function?

Right(target_cell, Len(target_cell) - 1)

What do you get with the following?

Debug.Print Asc(Mid(target_cell, 1, 1))

 

[SkipVought]

Are you sure that its a SPACE? use the CODE fuction to verify.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

Sometimes text values contain leading, trailing, or multiple embedded space characters (Unicode character set (Unicode: A character encoding standard developed by the Unicode Consortium. By using more than one byte to represent each character, Unicode enables almost all of the written languages in the world to be represented by using a single character set.) values 32 and 160), or non-printing characters (Unicode character set values 0 to 31, 127, 129, 141, 143, 144, and 157). These characters can sometimes cause unexpected results when you sort, filter, or search. For example, users may make typographical errors by inadvertently adding extra space characters, or imported text data from external sources may contain nonprinting characters embedded in the text. Because these characters are not easily noticed, the unexpected results may be difficult to understand. To remove these unwanted characters, you can use a combination of the TRIM, CLEAN, and SUBSTITUTE functions.

The TRIM function removes spaces from text except for single spaces between words. The CLEAN function removes all nonprintable characters from text. Both functions were designed to work with 7-bit ASCII, which is a subset of the ANSI character set (ANSI character set: An 8-bit character set used by Microsoft Windows that allows you to represent up to 256 characters (0 through 255) by using your keyboard. The ASCII character set is a subset of the ANSI set.). It's important to understand that the first 128 values (0 to 127) in 7-bit ASCII represent the same characters as the first 128 values in the Unicode character set.

The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160. This character is commonly used in Web pages as the HTML entity,  . By itself, the TRIM function does not remove this nonbreaking space character.

The CLEAN function was designed to remove the first 32 non-printing characters in the 7 bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters.

To do this task, use the SUBSTITUTE function to replace the higher value Unicode characters with the 7-bit ASCII characters for which the TRIM and CLEAN functions were designed.

It goes on to provide some examples.

 

[jrobin5881]

Sorry for the delayed response. Weather held me up. Thank you all for your help. The trim function had always worked for me in the past so this one kind of threw me.

I ended up using the formula: =TRIM(SUBSTITUTE(B7,CHAR(160),CHAR(32))) in a colum and it worked fine.
Thanks again.