+ Reply to Thread
Results 1 to 2 of 2

Preceding a number by zeros, that is still a number

  1. #1
    Michele
    Guest

    Preceding a number by zeros, that is still a number

    I get send spreadsheets with supplier numbers on them, i.e. 256. i wish to
    change these to 0000256, as all our account numbers contain 7 digits.
    I know i can do this by either putting a ' before the number or changing the
    cell to text, however i wish to change all numbers in a click.
    I discovered i could do this by using the format cell custom function. This
    indeed changes all my supplier codes to a 7 digit number. However this number
    i.e. 0000256 is displayed as 256 in the formula bar, which does not help me
    as i need to use these supplier number against other sheets using vlookup. as
    it is not seen as a number, or seen as the number 256, vlookup does not
    recognise it as 0000256 but as 256 so i cannot use the function.
    Please help

  2. #2
    Arvi Laanemets
    Guest

    Re: Preceding a number by zeros, that is still a number

    Hi

    Into another column, enter the formula like (the example is for row 2,
    assuming your original number in cell A2):
    =TEXT(A2,"0000000")
    Copy the formula down, and then replace with values (PasteSpecial Values).


    --
    Arvi Laanemets
    ( My real mail address: arvil<at>tarkon.ee )



    "Michele" <Michele@discussions.microsoft.com> wrote in message
    news:02AEF410-DACA-47ED-8B6F-591980AE016B@microsoft.com...
    >I get send spreadsheets with supplier numbers on them, i.e. 256. i wish to
    > change these to 0000256, as all our account numbers contain 7 digits.
    > I know i can do this by either putting a ' before the number or changing
    > the
    > cell to text, however i wish to change all numbers in a click.
    > I discovered i could do this by using the format cell custom function.
    > This
    > indeed changes all my supplier codes to a 7 digit number. However this
    > number
    > i.e. 0000256 is displayed as 256 in the formula bar, which does not help
    > me
    > as i need to use these supplier number against other sheets using vlookup.
    > as
    > it is not seen as a number, or seen as the number 256, vlookup does not
    > recognise it as 0000256 but as 256 so i cannot use the function.
    > Please help




+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1