+ Reply to Thread
Results 1 to 5 of 5

Newbie Question: Using Excel to work with output of PI - break sets of numbers, remove num

  1. #1
    Registered User
    Join Date
    10-19-2014
    Location
    Dublin, Ireland
    MS-Off Ver
    2013
    Posts
    5

    Newbie Question: Using Excel to work with output of PI - break sets of numbers, remove num

    Hi,

    I'm a newbie here, and to excel at this level, and have a question I hope somebody can help with.

    I have a list of numbers outputted from a review of PI (the number 3.1415926535897932384......)

    It looks like this:

    8850838032 0621312483 8327044318 1257233570 9958940293 1391776730 2923888859 5836058683 5192760238 4694561699 : 110000000001
    9312900154 4838526183 9375914106 9846458403 5847003707 2451543553 9394699328 5157228504 5434270590 6509736487 : 110000000002
    1284090545 3175919151 4159855781 3410862263 2549812643 7600394225 7109902021 0694219181 6542482795 7164656581 : 110000000003
    1367977800 8915483236 6072599505 1466161901 1090687303 7608155585 3289637107 6490574006 0401938787 7258319674 : 110000000004

    - the list is in notepad (.txt file)
    - set of 10 digits per number
    - set of 10 numbers per line (10 digits per x 10 numbers per line)
    - reference number at the end ( the : 110000000001 : 110000000002 etc numbers)


    Heres what I would like to do:

    - to remove the reference numbers first ( the : 110000000001 : 110000000002 etc numbers)
    - to break each set of 10 digits into 5 sets of 2 digit numbers, with each 2 digit number appearing on a seperate line of its own - 0621312483 to 06 21 31 24 83
    - from this I want to remove all 2 digit numbers over 26, so that I am only left with numbers from 01 - 26 - from 06 21 31 24 83 to 06 21 24

    Any ideas of how to do this in excel?

    Any assistance, suggestions, ideas welcome.

    Thanks

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Newbie Question: Using Excel to work with output of PI - break sets of numbers, remove

    Open Excel
    Choose the Open File dialogue
    Select Text Files as the type of file
    Select the Text File with the data
    Select Delimited
    Choose space and other: semicolon as the delimiters
    for column 11, format as text
    Convert


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Newbie Question: Using Excel to work with output of PI - break sets of numbers, remove

    Hi

    This is fairly basic.

    All you need to do is to import your file into excel
    delete columns 11 and 12
    import the remaining data into an array in VBA
    use a triple loop to look through all your data and save your valid data into a new array
    then write the new array back to excel.
    simples.

    this macro will do it for you. But you need to change the second line to let it work

    make sure your notepad file is named test.txt. No empty lines at the top of the notepad file.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 10-19-2014 at 07:15 PM.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Newbie Question: Using Excel to work with output of PI - break sets of numbers, remove

    I took the text from your example in mssg #1 and used text to columns to split the values into pairs in rows 1:4

    In Row 15 I applied a formula to extract only the values from 1 to 26 eliminating all blanks in the process.

    I hope this helps.

    The ARRAY formula used is:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Newbie Question: Using Excel to work with output of PI - break sets of numbers, remove

    robbertly,

    I have posted a solution to your latest request concerning the output to multiple columns, my latest macro BreakSetsInto250Rows at the following link:

    http://www.mrexcel.com/forum/excel-q...e-numbers.html


    A message to forum cross posters.

    Please read this:
    http://www.excelguru.ca/node/7
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. To Insert/Remove a Page Break in Excel 2007
    By ExcelTip in forum Tips and Tutorials
    Replies: 9
    Last Post: 06-08-2015, 01:23 AM
  2. [SOLVED] Newbie-question: recorded macro does not work - why?
    By Michael Moser in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2006, 02:15 PM
  3. Remove page break in Excel?
    By Page Breaks in Excel Removal in forum Excel General
    Replies: 1
    Last Post: 09-12-2005, 07:05 PM
  4. [SOLVED] Excel Newbie Question Please Help
    By Thomas Grassi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-09-2005, 06:06 PM
  5. [SOLVED] how do Ii remove the grey page numbers in a work sheet
    By parker1230 in forum Excel General
    Replies: 4
    Last Post: 02-02-2005, 09:06 PM

Tags for this Thread

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