How do I eliminate spaces/characters from the beginning of a cell

[Guest]

I have a macro that has hit a snag. On one of the pages of the workbook, I
have a list of product codes. Some are copied over and sort fine. Others, the
same codes, are copied with a leading space or a " ' " before the code. Is
there an If then statement I can use that will remove any leading spaces or
this symbol? It's messing up a sort that the entire macro depends on. Not all
of the product codes have these spaces or characters which is why I need an
if then statement to do nothing if they aren't there and to remove them if
they are. Thanks in advance for any help you can offer.

 

[Biff]

Hi!

Try to identify what these symbols are.

A1 = product code with a leading "space" or " ' "

B1 = CODE(A1)

B1 will return the char code for the first char in cell A1.

Once you have them identified then you can use Edit>Replace to remove them.

For example, the code formula returns 160 which is the char code for a
non-breaking space.

Select the range of product codes then goto Edit>Replace

In Find What: hold down the ALT key and use the numeric keypad and enter:
0160. When you release the ALT key, you'll see what appears to be a space in
the Find what box. How do you see a space? The cursor moves to the right!

Now, in the Replace With box, leave this empty. don't enter anything. Then
click Replace All. So, what you're doing is replacing all the char 0160's in
the range with nothing.

I have to do this almost every time I copy data posted in these newsgroups
and then paste it into a worksheet to work on a solution to a question.

Do the same thing for the " ' " char.

Biff