Results 1 to 3 of 3

Thread: Adding new column with text starting with and x length long

  1. #1
    Seeker jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    19
    Articles
    0
    Excel Version
    2016

    Adding new column with text starting with and x length long



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Howdi,

    If I wanted to add a new custom column with the text from another column starting with "CR" for example (Below) and a certain then a certain string length eg CR00012345 (so 10 long).

    Thanks in advance

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,235
    Articles
    0
    Excel Version
    Office 365 Subscription
    Nothing attached.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    99
    Articles
    0
    Excel Version
    2013, 2016, O365
    With these values in an Excel Table named Table1:
    Code:
    OrigVal
          1
         12
        123
    
      66778
    1234567

    This M-Code creates a new column that prepends "CR" and enough zeros to make a 10 character field value:
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrigVal", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "NewVal", each "CR" & Text.End("0000000000" & Text.From([OrigVal]),8))
    in
        #"Added Custom"
    These are the results:
    Code:
    OrigVal       NewVal
          1        CR00000001
         12        CR00000012
        123        CR00000123
           
      66778        CR00066778
    1234567        CR01234567
    Is that something you can work with?
    Ron Coderre
    Former Microsoft MVP (2006-2015)

Posting Permissions

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