+ Reply to Thread
Results 1 to 4 of 4

Removing numbers from the beginning of a text string

  1. #1
    Night Owl
    Guest

    Removing numbers from the beginning of a text string

    I have a long list of addresses, some of which start with a house number
    then a comma. Is there a (simple) way of finding the position of the comma
    within the first (say) 5 characters of the string, and, if it exists, remove
    everything to the left of and including the comma, leaving the rest of the
    text string?

    Thanks in advance,

    Pete



  2. #2
    William
    Guest

    Re: Removing numbers from the beginning of a text string

    Hi Night Owl

    Doubtless there are better ways, but if the address is in A2, enter the
    following in B2 and copy down.

    =IF(ISERROR(FIND(",",LEFT(A2,5),1)),A2,TRIM(RIGHT(A2,LEN(A2)-FIND(",",LEFT(A2,5),1))))

    --


    XL2003
    Regards

    William
    [email protected]


    "Night Owl" <[email protected]> wrote in message
    news:%[email protected]...
    >I have a long list of addresses, some of which start with a house number
    >then a comma. Is there a (simple) way of finding the position of the comma
    >within the first (say) 5 characters of the string, and, if it exists,
    >remove everything to the left of and including the comma, leaving the rest
    >of the text string?
    >
    > Thanks in advance,
    >
    > Pete
    >





  3. #3
    Max
    Guest

    Re: Removing numbers from the beginning of a text string

    Assuming data is in col A, A1 down

    Try in B1:
    =IF(ISNUMBER(SEARCH(",",TRIM(A1))),TRIM(MID((TRIM(A1)),SEARCH(",",TRIM(A1))+
    1,99)),TRIM(A1))
    Copy down

    The "99" within MID is arbitrary.
    Adjust to a higher number if necessary
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "Night Owl" <[email protected]> wrote in message
    news:#[email protected]...
    > I have a long list of addresses, some of which start with a house number
    > then a comma. Is there a (simple) way of finding the position of the

    comma
    > within the first (say) 5 characters of the string, and, if it exists,

    remove
    > everything to the left of and including the comma, leaving the rest of the
    > text string?
    >
    > Thanks in advance,
    >
    > Pete
    >
    >




  4. #4
    Night Owl
    Guest

    Re: Removing numbers from the beginning of a text string

    Thanks for your replies, guys. I've managed to sort this, now. Thank you.

    You wouldn't mind having a look at my next post, would you :-)

    Peter

    "Night Owl" <[email protected]> wrote in message
    news:%[email protected]...
    >I have a long list of addresses, some of which start with a house number
    >then a comma. Is there a (simple) way of finding the position of the comma
    >within the first (say) 5 characters of the string, and, if it exists,
    >remove everything to the left of and including the comma, leaving the rest
    >of the text string?
    >
    > Thanks in advance,
    >
    > Pete
    >




+ 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