+ Reply to Thread
Results 1 to 7 of 7

Format Values

  1. #1
    Registered User
    Join Date
    05-16-2013
    Location
    USA
    MS-Off Ver
    Excel 2010 (Work) 2016 (Home)
    Posts
    10

    Format Values

    Hello.

    I am trying to format data an excel file cells to keep zeros and format numbers like locker combinations... I have the field value 4-88-8 and I need to create locker
    combination formats which are like 04-88-08. Using custom format. Is there any way, using format cells or VBA that I can format these values as locker combinations, 2 digits separated by a dash? I have thousands of these records I would rather not manually key.

    Thanks!

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Format Values

    With your 4-88-8 in cell A1, put this where you want the 04-88-08 result:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    But watch out for date-like numbers!

    Depending on what the original numbers are and what your system views as a valid date, some numbers may be treated by Excel as a date - for example 10-10-9 would be treated as 10th October 2009 and displayed according your short-date format (probably 10 10 09 or 10/10/09). If this happens then the formula above will give you an error. If Excel thinks the #-#-# is a date, then this formula will convert it to the format you want:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    (This assumes you're using the USA m-d-y format, not the international d-m-y format.)
    But this 2nd formula won't work correctly for numbers which are not dates, so you need to combine the two like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Hope that all makes sense and does what you want.
    Last edited by Aardigspook; 07-24-2019 at 03:44 PM.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Registered User
    Join Date
    02-08-2017
    Location
    Virginia
    MS-Off Ver
    2010
    Posts
    77

    Re: Format Values

    Hello! I think I have something that will work for you.

    First, you will need to use Text To Columns to separate what you currently have into three adjacent cells. To do this: Select your range that has all of your combinations. Then go to Data > Text To Columns. In the pop up box, select "Delimited" then press "Next". On the left side, remove the checkbox next to Tab, and press the checkbox next to "Other". In the field to the right of it, put the dash symbol there, then press "Next". Delete what is in the Destination field, and change it to be whatever cell is next to your topmost combination. So if 4-88-8 is in A1, set the destination to be A2 and press finish. Now, all of your numbers should be in three separate, adjacent columns...Phew, that was the hard part. Now, paste the formula below to the right of the third number:

    =IF(A2<10,0&A2,A2)&"-"&IF(B2<10,0&B2,B2)&"-"&IF(C2<10,0&C2,C2)

    This is assuming your first combination is in A1, B2 and C2. I have attached a file that has this formula in place already for you to use however you want.

    Thanks.
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Format Values

    This should work:

    =TEXT(LEFT(SUBSTITUTE(A1,"-"," "),FIND(" ",SUBSTITUTE(A1,"-"," "))-1),"00")&"-"&TEXT(MID(SUBSTITUTE(A1,"-"," "),FIND(" ",SUBSTITUTE(A1,"-"," "))+1,2),"00")&"-"&TEXT(RIGHT(SUBSTITUTE(A1,"-"," "),2),"00")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Format Values

    @Ali - your formula, like my first one, relies on the numbers not being 'recognised' by Excel as a date. Try it after entering 10-10-9 and see what you get (I get a #Value error). That's why I suggested the IfError addition in my final formula. If you know a better way of dealing with it, that would be great.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Format Values

    If the numbers in A1 are entered with a preceding apostrophe it works fine.

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Format Values

    @Ali. Agreed, or pre-format the cells as text of course. I was trying not to assume too much for the OP.

    @jcraig713. If you have your entries as 'text' then either Ali's formula or my first one will work fine and you can ignore the rest of my post.

+ 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 two different format/values into one format
    By Charles33 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-29-2019, 01:50 PM
  2. [SOLVED] Convert Cell values with fixed format values
    By Parth007 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-23-2016, 08:38 AM
  3. Concatenate row values on 2 sheets and conditional format where concatenated values <>
    By mosesthetank in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-27-2015, 02:06 PM
  4. Replies: 4
    Last Post: 07-27-2015, 02:21 AM
  5. [SOLVED] Search for multiple string values in the first row of a table and format column values.
    By Excel_junky in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-12-2013, 12:48 PM
  6. Replies: 2
    Last Post: 10-01-2012, 10:00 PM
  7. Keep last row format/values when transposing values from one worksheet to another
    By BuzzOffSweetheart in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-18-2012, 02:27 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