Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Split out an element of lines of numbers

  1. #1
    Board Regular
    Join Date
    Feb 2014
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Split out an element of lines of numbers

    Hi - I have received a block of data which has surrounded the key information that I require.

    Let me explain 11111177777777772017-12-11 is recorded in a column called Customer Number

    I require the Customer Number which the ten 7's, of course they not be 7 as each customer will have a unique customer number.

    How can I create a separate column by extracting this information. It would also be good to get the Region code which the Six 1's into another column

    As you can see the final 6 numbers reflect the date and I do not need

    Any help greatly appreciated.

    Thanks

  2. #2
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    3,464
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split out an element of lines of numbers

    Hi,

    If your Region Code is always 6 digits, and your Customer Number is always 10 digits:

    ABCD
    1Region CodeCustomer #
    211111177777777772017-12-111111117777777777
    312345678901234562017-11-121234567890123456

    Sheet1



    Worksheet Formulas
    CellFormula
    C2=LEFT(A2,6)
    D2=MID(A2,7,10)



    C2, D2 formulas copied down.

  3. #3
    Board Regular
    Join Date
    Feb 2014
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split out an element of lines of numbers

    Perfect - thank you

  4. #4
    Board Regular
    Join Date
    Jul 2017
    Posts
    287
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split out an element of lines of numbers

    You could also use Text to Columns

    1. Data>>Text to Columns
    2. Fixed Width >> Next
    3. Click to enter split points before and after the customer ID >> Next
    4. Click any columns you don't want (i.e the 'date' part and the first part) and choose don not import
    5. If you want to replace the long number leave the destination be, if not choose an adjacent column >> Finish

    You data is now split.
    Work: Excel 2016 on Windows 10
    Home: Office 365 Insider on Windows 10

    Please use [code][/code] tags

  5. #5
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    3,464
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split out an element of lines of numbers

    Quote Originally Posted by philb99 View Post
    Perfect - thank you
    You're welcome.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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