Results 1 to 7 of 7

Thread: Append the number 4 and enough Zeros to make all numbers 6 digits long

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Acolyte GreenBoy's Avatar
    Join Date
    Apr 2015
    Posts
    24
    Articles
    0
    Excel Version
    Office 365

    Question Append the number 4 and enough Zeros to make all numbers 6 digits long

    Im having a very long week and i think my brain needs re-booting.

    I know this should be fairly easy but for the life of me i cant get this to work.

    I have a series of site numbers which range from 1 to 5 digits in length - i.e. 1 to 99999

    What i need to do is make them all 6 digits long and start with a 4, so 1 would become 400001 and 175 would be 400175 all the way up to 499999

    Can any kind person point me at the right functions to do this?

    I am assuming Text.PadStart is used but need to calc the number of zeros needed and then do the same for a single number 4 at the start, but the calculating the number of Zeros to add to dynamically add a different amount per site number is escaping me.

    Thanks in advance for any assistance, comments etc
    Dave

  2. #2
    Acolyte GreenBoy's Avatar
    Join Date
    Apr 2015
    Posts
    24
    Articles
    0
    Excel Version
    Office 365
    Ok - so fresh Coffee was what i needed.

    The solution is a lot simpler than i was thinking,

    Code:
    Text.PadStart(Text.PadStart(Number.ToText([CSR Number]),5,"0"),6,"4")
    Seems to work , so thanks for reading.
    Dave

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,360
    Articles
    46
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi GreenBoy,

    You can simplify this a bit, avoiding a second call to the Text.PadStart function:

    Code:
    "4" & Text.PadStart(Number.ToText([CSR Number]),5,"0")
    Cheers,
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Acolyte macropod's Avatar
    Join Date
    Mar 2017
    Posts
    50
    Articles
    0
    Excel Version
    2010
    Why not something along the lines of:
    .Range("A1").Value + 4000000
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    Acolyte GreenBoy's Avatar
    Join Date
    Apr 2015
    Posts
    24
    Articles
    0
    Excel Version
    Office 365
    Ken and Macropod - thanks for the replied - both work great.

    I did say it was a long week !!

    Thanks for the solutions - you are awesome!.
    Dave

  6. #6
    Conjurer Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    110
    Articles
    0
    Excel Version
    2013, 2016, O365
    A bit late to this one...but...Maybe this?:
    Code:
    Number.ToText([CSR Number],"400000")
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,360
    Articles
    46
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Ron, that's a clever one!
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •