+ Reply to Thread
Results 1 to 21 of 21

Copy numbers that are being custom formatted

  1. 11-13-2009, 04:37 PM #1
    mame
    mame is offline
    Registered User
    Join Date
    08-14-2009
    Location
    No
    MS-Off Ver
    Excel 2003
    Posts
    20

    Copy numbers that are being custom formatted

    Column A has a custom format of 000. Therefore the numbers in that column always have leading zeroes if they're less than 3 digits long.

    I want to copy these leading zeroes to another column, but I DON'T want to have to custom format the new column. I just want the values copied over with any leading zeroes, if applicable.

    When I "Paste Special" and select "Values", numbers like "1" that show up as "001" in Column A will show up as "1" again in the new column. How can I get the actual value "001" to be all that's in the new column?

    If it's easier for you, pretend I need to copy over these custom formatted values in to another program which does not retain any custom formatting.

    Thanks!
    Reply With QuoteRegister To Reply
  2. 11-13-2009, 04:48 PM #2
    ron2k_1
    ron2k_1 is offline
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: How do I copy numbers that are being custom formatted?

    And using a formula wouldn't work? Cause if it doens't matter to you, then there are several ways to do that. One of them would be to just have a simple cell reference, i.e., =A1, etc.

    Please advice further...

    Ron
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated
    Reply With QuoteRegister To Reply
  3. 11-13-2009, 04:49 PM #3
    xenixman
    xenixman is offline
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Rowley
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: How do I copy numbers that are being custom formatted?

    How can I get the actual value "001" to be all that's in the new column?


    The same way you got the numbers to retain the leading zeros in column A.
    Why did you have to do it in column A?

    Unless you want to make them TEXT.
    Reply With QuoteRegister To Reply
  4. 11-13-2009, 04:53 PM #4
    mame
    mame is offline
    Registered User
    Join Date
    08-14-2009
    Location
    No
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: How do I copy numbers that are being custom formatted?

    Quote Originally Posted by ron2k_1 View Post
    And using a formula wouldn't work? Cause if it doens't matter to you, then there are several ways to do that. One of them would be to just have a simple cell reference, i.e., =A1, etc.

    Please advice further...

    Ron
    Although "1" appears as "001", if you click on the cell it still has just the value of "1". I need it to actually be "001", not just appear that way.
    Reply With QuoteRegister To Reply
  5. 11-13-2009, 05:02 PM #5
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: How do I copy numbers that are being custom formatted?

    If you want it to "be" 001 instead of 1, then it is no longer seen as a number. you would use the formula (e.g. in B1) 
    Please Login or Register  to view this content.
    If you want to remove the formula, you'd then Copy>Paste Special>Values over top of the formula.
    Last edited by ChemistB; 11-13-2009 at 05:06 PM.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  6. 11-13-2009, 05:06 PM #6
    mame
    mame is offline
    Registered User
    Join Date
    08-14-2009
    Location
    No
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: How do I copy numbers that are being custom formatted?

    But if I compare B1 to 001, e.g., IF(B1=001), then it will fail, right? I want 001 to appear in B1. Excel is being silly.
    Last edited by shg; 11-13-2009 at 05:20 PM. Reason: deleted spurious quote
    Reply With QuoteRegister To Reply
  7. 11-13-2009, 05:11 PM #7
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: How do I copy numbers that are being custom formatted?

    But if I compare B1 to 001, e.g., IF(B1=001), then it will fail, right?
    Correct! If in another cell, you wish to compare them, then you would need the formula 
    Please Login or Register  to view this content.
    Why is it so important for the leading zeros to be real instead of virtual?
    Reply With QuoteRegister To Reply
  8. 11-13-2009, 06:43 PM #8
    mame
    mame is offline
    Registered User
    Join Date
    08-14-2009
    Location
    No
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: How do I copy numbers that are being custom formatted?

    I need them to be real because I may convert it to a CSV or copy the values out in to another program that won't retain the formatting to put the leading 0's there in the first place.

    How can I get the 0's there. This should be a trivial thing but it seems very very hard.
    Last edited by shg; 11-13-2009 at 07:36 PM. Reason: deleted spurious quote
    Reply With QuoteRegister To Reply
  9. 11-13-2009, 07:37 PM #9
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Copy numbers that are being custom formatted

    Mame, please don't quote whole posts.

    Thanks.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  10. 11-13-2009, 07:38 PM #10
    mame
    mame is offline
    Registered User
    Join Date
    08-14-2009
    Location
    No
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Copy numbers that are being custom formatted

    Quote Originally Posted by shg View Post
    Mame, please don't quote whole posts.

    Thanks.
    Can you help me please?
    Reply With QuoteRegister To Reply
  11. 11-13-2009, 07:47 PM #11
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Copy numbers that are being custom formatted

    Skip the custom number format and format the cells as text. Enter leading zeros as required. When you copy them, they will copy as text.
    Reply With QuoteRegister To Reply
  12. 11-13-2009, 07:50 PM #12
    mame
    mame is offline
    Registered User
    Join Date
    08-14-2009
    Location
    No
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Copy numbers that are being custom formatted

    Won't that cause a problem?
    Last edited by shg; 11-13-2009 at 08:08 PM. Reason: deleted spurious quote
    Reply With QuoteRegister To Reply
  13. 11-13-2009, 07:52 PM #13
    xenixman
    xenixman is offline
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Rowley
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: Copy numbers that are being custom formatted

    This should be a trivial thing but it seems very very hard.

    It is trivial, simply custom format as 000 or enter them as text.

    I DON'T want to have to custom format the new column

    I'm curious, is there a specific reason you do not want to custom format?
    Reply With QuoteRegister To Reply
  14. 11-13-2009, 07:57 PM #14
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Copy numbers that are being custom formatted

    Mame, recognize that numbers don't have leading zeros; if they did, where would you stop?

    Things that do have leading zeros are text, so store them that way.
    Reply With QuoteRegister To Reply
  15. 11-13-2009, 07:57 PM #15
    mame
    mame is offline
    Registered User
    Join Date
    08-14-2009
    Location
    No
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Copy numbers that are being custom formatted

    @xenixman: Copy / Paste in to another program would not carry the formatting over.
    Last edited by shg; 11-13-2009 at 08:03 PM. Reason: deleted spurious quote
    Reply With QuoteRegister To Reply
  16. 11-13-2009, 07:58 PM #16
    mame
    mame is offline
    Registered User
    Join Date
    08-14-2009
    Location
    No
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Copy numbers that are being custom formatted

    @shg: OK. I will try that and let you know if this is a good or bad thing.
    Last edited by shg; 11-13-2009 at 08:02 PM. Reason: deleted spurious quote
    Reply With QuoteRegister To Reply
  17. 11-13-2009, 08:02 PM #17
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Copy numbers that are being custom formatted

    mame, I asked you not to quote whole posts, given you an infraction for repeating it when I asked you not to, and you've just done it twice again.

    If you don't understand, please ask. If you do, then please Stop. Use the Reply button, not the Quote button.

    Thank you.
    Last edited by shg; 11-13-2009 at 08:15 PM.
    Reply With QuoteRegister To Reply
  18. 11-13-2009, 08:16 PM #18
    xenixman
    xenixman is offline
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Rowley
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: Copy numbers that are being custom formatted

    If you custom format as 000,
    enter them as TEXT or
    even enter them as a formula such as ="001"
    then copy / paste to Word, or even Notepad they will retain the leading zeros.
    Reply With QuoteRegister To Reply
  19. 11-13-2009, 08:18 PM #19
    mame
    mame is offline
    Registered User
    Join Date
    08-14-2009
    Location
    No
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Copy numbers that are being custom formatted

    Quote Originally Posted by shg View Post
    mame, I asked you not to quote whole posts, given you an infraction for repeating it when I asked you not to, and you've just done it twice again.

    If you don't understand, please ask. If you do, then please Stop. Use the Reply button, not the Quote button.

    Thank you.
    Don't worry friend
    Reply With QuoteRegister To Reply
  20. 11-13-2009, 08:19 PM #20
    mame
    mame is offline
    Registered User
    Join Date
    08-14-2009
    Location
    No
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Copy numbers that are being custom formatted

    Quote Originally Posted by xenixman View Post
    If you custom format as 000,
    enter them as TEXT or
    even enter them as a formula such as ="001"
    then copy / paste to Word, or even Notepad they will retain the leading zeros.
    Great, will try!
    Reply With QuoteRegister To Reply
  21. 11-17-2009, 02:22 PM #21
    mame
    mame is offline
    Registered User
    Join Date
    08-14-2009
    Location
    No
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Copy numbers that are being custom formatted

    Quote Originally Posted by shg View Post
    mame, I asked you not to quote whole posts, given you an infraction for repeating it when I asked you not to, and you've just done it twice again.

    If you don't understand, please ask. If you do, then please Stop. Use the Reply button, not the Quote button.

    Thank you.
    Thanks for your help!
    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)

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