+ Reply to Thread
Results 1 to 9 of 9

How to auto convert last 4 digit number

  1. #1
    Registered User
    Join Date
    10-12-2011
    Location
    Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    94

    How to auto convert last 4 digit number

    Hi All,

    Good day. My card reader will capture 12 digit from employee ID card into excel sheet. Default card will be ID 88******1100, when the ID encoded to 88******1200, I still manage to capture the data by use REPLACE formula. But now when encoded card with last 4 digit become 1300,1400,1500,1600 ...my code unable to recognize anymore. Any IDEA / help very appreciated..

    Thank you

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How to auto convert last 4 digit number

    Hello,

    this is hard to understand. Please tell us:

    - What is in the cell?
    - What do you want the result to be?
    - What is the logic?

    What formula are you using?

  3. #3
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: How to auto convert last 4 digit number

    What is your code?

  4. #4
    Registered User
    Join Date
    10-12-2011
    Location
    Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    94

    Re: How to auto convert last 4 digit number

    Hi Teylyn,

    I've attached dummy file. You may refer to sheet "data".
    - What is in the cell?
    The cell value will be 12 digit
    - What do you want the result to be?
    Maybe VBA logic to convert the encoded to last 4 digit to 1100.
    - What is the logic?
    We have a lot of our employee had encoded their ID card to HR. My program is for dummy PC & data is not up to date with HR.

    I just need the program able to capture all ID card no even with different encoded ID.

    tq
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: How to auto convert last 4 digit number

    Actually your requirement is still confusing to me. Please use reference to describe. Do you want to add 1100 after every value of column C at Namelist sheet? Or you want to remove 1100 from every value of Column A at Namelist Sheet?

  6. #6
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How to auto convert last 4 digit number

    Convert the numeric digits to text, then you can split off the last four characters and replace them with other characters. The numbers are very large, so you may want to handle them as text anyway, since scientific notation is not helpful.

  7. #7
    Registered User
    Join Date
    10-12-2011
    Location
    Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    94

    Re: How to auto convert last 4 digit number

    Hi Sanram, every single ID card will contain 12 digit. The original ID card last 4 digit will be 1100, but in any case the card is damage or missing HR will issue new encoded card with last 4 digit to 1200 (1st time), 1300 (3rd time)...
    The original employee number still remain same ie; 88000012.
    880000121100 (1st issued)
    880000121200 (2nd time issued)

    Sorry for very late reply... away from office without internet access.
    tq.

  8. #8
    Registered User
    Join Date
    10-12-2011
    Location
    Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    94

    Re: How to auto convert last 4 digit number

    Hi Sanram, every single ID card will contain 12 digit. The original ID card last 4 digit will be 1100, but in any case the card is damage or missing HR will issue new encoded card with last 4 digit to 1200 (1st time), 1300 (3rd time)...
    The original employee number still remain same ie; 88000012.
    880000121100 (1st issued)
    880000121200 (2nd time issued)

    Sorry for very late reply... away from office without internet access.
    tq.

    Sorry double posting...connection slow.
    Last edited by Dean Zuki; 09-22-2016 at 06:05 AM. Reason: Double posting

  9. #9
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: How to auto convert last 4 digit number

    Actually I am still confused. Anyway I have created a separate file with my understanding. See the attachment if you are trying something like that. There I have created 2 macros. One for individual and other for selected ID. It will make more sense if you see the file.
    For Individual the code is :
    Please Login or Register  to view this content.
    For Selected the code is :
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. convert to 2 digit number
    By pilotwings64 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-17-2019, 03:16 AM
  2. [SOLVED] Convert any number to 3 digit only
    By analystbank in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-28-2016, 07:32 AM
  3. Convert 3 digit or 4 digit number to time format
    By dubcap01 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2015, 04:30 AM
  4. [SOLVED] Convert number to two digit
    By mattress58 in forum Excel General
    Replies: 2
    Last Post: 10-29-2014, 10:10 AM
  5. Convert 4-5 digit number to time HH:MM:SS
    By scaffdog845 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-19-2012, 01:53 PM
  6. convert 9 digit number into day and time
    By cords in forum Excel General
    Replies: 7
    Last Post: 03-17-2012, 12:45 AM
  7. How do I make a cell auto. convert a 6 digit # into ##:##:##
    By Kosherboychief in forum Excel General
    Replies: 2
    Last Post: 01-31-2011, 12:25 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1