+ Reply to Thread
Results 1 to 13 of 13

How to remove the first 2 digits if they are certain numbers

  1. 01-29-2014, 03:01 PM #1
    ktech
    ktech is offline
    Registered User
    Join Date
    01-29-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    How to remove the first 2 digits if they are certain numbers

    Hi All,

    I have a excel file, I need to remove the first two digits if they are certain numbers, such as 12. For example, if the number is 12987654, then I need remove 12, and it will be "987654" , but if it is not 12 in the first two digits, then keep it no change, for example if it is 345678, then keep it.
    I barely work with Excel formulas, now I need connect the excel file with my Database table. I need to make the file matches the DB. Hope someone can help.
    Thank you so much for any helps and suggestions.
    Reply With QuoteRegister To Reply
  2. 01-29-2014, 03:25 PM #2
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,623

    Re: How to remove the first 2 digits if they are certain numbers

    simple suggestion for formula (if your numbers are in column A, write in B1:
    =if(left(A1,2)=12,mid(A1,3,1000),A1)
    copy down as needed
    then select all column B copy it and paste special as values into A. Delete B and here you have it

    Otherwise read in forum rules how to attach excel workbook to the post
    Best Regards,

    Kaper
    Reply With QuoteRegister To Reply
  3. 01-29-2014, 03:31 PM #3
    ckim551
    ckim551 is offline
    Registered User
    Join Date
    01-08-2014
    Location
    Irvine, CA ,USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: How to remove the first 2 digits if they are certain numbers

    I think you need to change 12 to "12"

    =IF(LEFT(A1,2)="12",MID(A1,3,1000),A1)

    Charles
    Reply With QuoteRegister To Reply
  4. 01-29-2014, 03:41 PM #4
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to remove the first 2 digits if they are certain numbers

    Or


    =IF(LEFT(A1,2)="12",--REPLACE(A1,1,2,""),A1)


    A
    B
    1
    12987654
    987654
    2
    345678
    345678
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  5. 01-29-2014, 03:49 PM #5
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to remove the first 2 digits if they are certain numbers

    This is another way if the string can contain letters also.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    If the string is only numerals and the result has to be a "number"

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  6. 01-29-2014, 04:00 PM #6
    chetu121
    chetu121 is offline
    Registered User
    Join Date
    01-24-2014
    Location
    Ahmedabad
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to remove the first 2 digits if they are certain numbers

    Dear Alkey,

    Kindly use this formula and see Pickute

    =IF(ISERROR(VALUE(MID(H3,1,2))),H3,RIGHT(H3,LEN(H3)-2))
    Capture.JPG
    Reply With QuoteRegister To Reply
  7. 01-29-2014, 04:05 PM #7
    chetu121
    chetu121 is offline
    Registered User
    Join Date
    01-24-2014
    Location
    Ahmedabad
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to remove the first 2 digits if they are certain numbers

    Dearl Alkey,

    Sorry for above post,

    use this formula

    =IF(VALUE(MID(G3,1,2))=12,RIGHT(G3,LEN(G3)-2),G3)
    Reply With QuoteRegister To Reply
  8. 01-29-2014, 04:39 PM #8
    ktech
    ktech is offline
    Registered User
    Join Date
    01-29-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: How to remove the first 2 digits if they are certain numbers

    Thank you so much to all!!
    Kaper's formula works well after added "" to 12 like Charles suggested.
    Now the only thing is when I copied column B , paste special as values into A, I got the green arrow for each cell. I need convert them to numbers. I tried format cell to number, but the green arrow still there.The file has thousands records. How can I convert them all to numbers?
    Thanks a lot!!
    Reply With QuoteRegister To Reply
  9. 01-29-2014, 04:57 PM #9
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to remove the first 2 digits if they are certain numbers

    See my formula in Post 4. It returns numeric value.
    Reply With QuoteRegister To Reply
  10. 01-29-2014, 05:04 PM #10
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to remove the first 2 digits if they are certain numbers

    The formulae that I gave you in #5 will handle that as will other answers such as #4
    Reply With QuoteRegister To Reply
  11. 01-29-2014, 05:27 PM #11
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,623

    Re: How to remove the first 2 digits if they are certain numbers

    Another option:
    =if(left(A1,2)="12",1*mid(A1,3,1000),A1)

    (of course 12 as text - so in "" - thanks Charles :-) )
    Reply With QuoteRegister To Reply
  12. 01-30-2014, 11:28 AM #12
    ktech
    ktech is offline
    Registered User
    Join Date
    01-29-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: How to remove the first 2 digits if they are certain numbers

    Thanks a lot!!
    It is working now. You all made my date.
    Again, thank you!
    Reply With QuoteRegister To Reply
  13. 01-30-2014, 11:42 AM #13
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to remove the first 2 digits if they are certain numbers

    Thanks for the feedback. It is great to have a solution.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Series of 8 character numbers - how to remove 1st 2 digits and last digit on each number.
    By martinmccoy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-22-2013, 02:28 PM
  2. [SOLVED] Remove last two digits from cell (not so easy)
    By ShareTheGlobe in forum Excel General
    Replies: 3
    Last Post: 07-26-2012, 09:22 PM
  3. Excel 2007 : How to remove first 3 digits from a CELL?
    By LearnerEXL in forum Excel General
    Replies: 10
    Last Post: 12-01-2009, 03:43 AM
  4. how to remove first x digits and last y digits from a cell?
    By maixent in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-28-2009, 02:06 AM
  5. [SOLVED] Remove ' if not followed by two digits
    By tom in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-09-2006, 02:10 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1