+ Reply to Thread
Results 1 to 4 of 4

remove first two letters if there is a space in cell

  1. #1
    Registered User
    Join Date
    02-01-2013
    Location
    melbourne
    MS-Off Ver
    Excel 2010
    Posts
    6

    remove first two letters if there is a space in cell

    Hi all
    Just a quick one i hope. Any way to solve this without a macro? I got a row with 10 to 13 digit numbers (some will have a letter in the end i want to keep) some will have two letters in front and i would like to remove the first two letters if they are presents and if not, just to display the number. ps there will always be a space between the first two letters and the number
    Example:
    074564368X
    0745622070
    WB 1118297709
    BP 9865434567654
    9865474567850

    Wanted result:
    074564368X
    0745622070
    1118297709
    9865434567654
    9865474567850

    Thanks in advance for the help

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: remove first two letters if there is a space in cell

    =trim(right(substitute(a1," ",rept(" ",10)),13))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    05-10-2013
    Location
    Pomáz, Hungary
    MS-Off Ver
    Excel 2007
    Posts
    78

    Re: remove first two letters if there is a space in cell

    Replace *_ (asterisk, space) with nothing.

  4. #4
    Registered User
    Join Date
    02-01-2013
    Location
    melbourne
    MS-Off Ver
    Excel 2010
    Posts
    6

    Smile Re: remove first two letters if there is a space in cell

    Quote Originally Posted by martindwilson View Post
    =trim(right(substitute(a1," ",rept(" ",10)),13))
    You sir are a freaking genius!! Thank you so much for the super quick answer. It works perfect and saved me a further 2h goggling for an answer Good to know that there are still people out there sacrificing there time to help others out. Thanks again

+ 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