+ Reply to Thread
Results 1 to 4 of 4

Transform the format of data

  1. #1
    Registered User
    Join Date
    05-15-2008
    Posts
    38

    Transform the format of data

    Hello,
    how could I transform this format of data:

    <Date>, <Time>, <Open>, <High>, <Low>, <Close>, <Volume>
    10/07/2008,5:39:00,1.5726,1.5726,1.5725,1.5725,0

    in this one?:
    <TICKER>,<PER>,<DTYYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT>
    EUR.USD,1,20080710,53900,1.5726,1.5726,1.5725,1.5725,0,0


    I thought in make a macro to create the new columns, but,

    1)how could I fill easy thousand of rows in the first column with the content “EUR.USD”, or the second column with “1”?
    2)How could I transform the date from “10/07/2008” to “20080710”?
    3)How could I tranform the hour from “5:39:00” to “53900”?

    Thank you very much for your help,
    Caldera

  2. #2
    Forum Contributor
    Join Date
    11-23-2007
    Location
    Suffolk, UK
    Posts
    298
    hello,

    1, add a new column, type in EUR.USD, copy it and click on the next cell under it, hold down shift and scroll down can click on the last cell in row you need and paste.
    2, highlight the whole column goto format/cell click on custom at the bottom type in on the right hand side yyyymmdd
    3, as above no 2 but put in hhmmss

    thanks reg

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi, assuming your data above is in A2:G2, enter the following formulae in I2:Q2

    Please Login or Register  to view this content.
    Now copy these formulae down all the rows.

    HTH

  4. #4
    Registered User
    Join Date
    05-15-2008
    Posts
    38

    Thank you

    Thank you very much!

    Both ways works perfect.

+ 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