+ Reply to Thread
Results 1 to 8 of 8

Data Entry

  1. #1
    Registered User
    Join Date
    10-31-2017
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    2

    Data Entry

    Hi guys and gals, I want to create a spreadsheet containing cells with fixed characters after every 5 manually entered characters:
    e.g. xxxxx-xxxxx-xxxxx-xxxxx-xxxxx
    So basically I want to type all 25 characters but have a "-" after every 5th character.

    Can anyone possibly assist me in doing this?

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Data Entry

    Are you ok with helper column? If yes, formula solution will be simplest to implement.

    Type in your value in A2(excluding "-"). Then in another column have following formula.
    =REPLACE(REPLACE(REPLACE(REPLACE(A2,6,,"-"),12,,"-"),18,,"-"),24,,"-")

    Other solution would need VBA.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Data Entry

    Using custom formats, set the format as #####-#####-#####-#####-#####
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    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: Data Entry

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

    v A B
    1 1234567981238765123546812 12345-67981-23876-51235-46812
    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

  5. #5
    Registered User
    Join Date
    10-31-2017
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    2

    Re: Data Entry

    Hi

    The 25 characters I want to enter consists of a mix of numbers & letters.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Data Entry

    Are the letters in the same location with each entry? Are they the same letter in the same position?

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Data Entry

    Would formula I provided not work? There's no Custom Format solution for mix of numbers and letters that does not follow very specific pattern.
    (i.e. letters must always occur at same position).

    Also, drawback to custom format solution is that often it isn't interpreted correctly by 3rd party software when imported etc.

  8. #8
    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: Data Entry

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

    v A B
    1 AN123OIUI55SSUIUI45OGOISD AN123-OIUI5-5SSUI-UI45O-GOISD

+ 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. [SOLVED] Stop entry overwrite and clear cell data off of entry in another cell
    By COURTTROOPER in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-26-2017, 02:33 PM
  2. Replies: 0
    Last Post: 02-04-2014, 12:36 AM
  3. Replies: 4
    Last Post: 02-01-2013, 10:18 AM
  4. how to entry same number twice when I alr set cannnot entry duplicate data.
    By Adamlearnexcel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-05-2012, 09:44 PM
  5. Vba for move data & clear entry screen for entry new data..
    By santosh226001 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-27-2012, 09:19 PM
  6. Replies: 0
    Last Post: 10-06-2011, 07:03 AM
  7. How do I prevent data entry in one cell if an entry exist in another cell
    By rkjudy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-13-2010, 09:38 PM

Tags for this Thread

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