+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Biff
    Guest

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

    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

    "A Waller" <[email protected]> wrote in message
    news:[email protected]...
    >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.
    > --
    > A Waller




  2. #2
    A Waller
    Guest

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

    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.
    --
    A Waller

  3. #3
    Biff
    Guest

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

    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

    "A Waller" <[email protected]> wrote in message
    news:[email protected]...
    >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.
    > --
    > A Waller




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1