+ Reply to Thread
Results 1 to 10 of 10

Convert text to number and change date format in a macro.

  1. 01-14-2014, 09:45 AM #1
    Zimmerray1
    Zimmerray1 is offline
    Registered User
    Join Date
    11-22-2013
    Location
    USA
    MS-Off Ver
    2003, 2007, 2010, Office 365
    Posts
    32

    Convert text to number and change date format in a macro.

    My Excel version - 2010. I have recorded a macro to manipulate, sort and delete unneeded gobblygook data that I receive from a JDEdwards export. It works perfectly but there are a couple more steps I need to happen which I now have to do manually. I end up with 3 columns of data - A, B, and H, that I have to convert to numbers. Column "A" is actually a date that I have to convert to 20XX and then format it to XX/XX/XX (ie 01/14/14).

    When I tried to do these things while recording my macro, I was made aware of the fact that those steps are not allowed. Attached is a sample of the JDE exported data I receive daily and a copy of my macro vba. I would greatly appreciate if someone could help me with code that will do these three things:

    1. Convert column "A" to 20XX date.
    2. Format columnn "A" date to XX/XX/XX.
    3. Convert columns "B" and "H" to text.



    Sub DataSort.doc Test.xlsx

    Best regards!
    Reply With QuoteRegister To Reply
  2. 01-14-2014, 11:34 AM #2
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,995

    Re: Convert text to number and change date format in a macro.

    Column A is the string 0050, not a date. The date is in B. I can only guess what the references to B and H are supposed to be. Please check the column identifiers in your description.

    Is the JDEdwards export file a .txt file, or a .xlsx file exactly like what you attached?

    Also, it would be so much easier if you attached the Excel file that has the macro, rather than copying the macro into a Word file and attaching that.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  3. 01-14-2014, 12:39 PM #3
    Zimmerray1
    Zimmerray1 is offline
    Registered User
    Join Date
    11-22-2013
    Location
    USA
    MS-Off Ver
    2003, 2007, 2010, Office 365
    Posts
    32

    Re: Convert text to number and change date format in a macro.

    Jeff,

    You are correct in your statement about the xlsx "original" file. That is the file I run the macro on. I should have explained that after the Macro is run, the date is then in column "A" and columns "B" and "H" need converting to numbers. That is part of the sort and manipulate data that I spoke of.

    The JDEdwards file is exported as the exact xlsx file I have attached.


    It is not a "file" that has the macro. This macro is in my Personal.xlbs and can be used whenever needed. The file I use it in changes on a daily basis. I tried to upload module1.bas but that didn't work. Thus, I copied it over into Word and uploaded that file. I'm open for suggestions.

    By the way, here is what the file looks like after the macro has been invoked...


    Test a.xlsx
    Last edited by Zimmerray1; 01-14-2014 at 12:48 PM. Reason: Adding attachment
    Reply With QuoteRegister To Reply
  4. 01-14-2014, 03:22 PM #4
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,995

    Re: Convert text to number and change date format in a macro.

    I see. You can paste the code directly into your post and mark it with CODE tags. Here is an update to your code that cleans up some macro recorder issues and adds the date cleanup, plus formats columns B and H as text:

    Edit: By the way, I tested it on your files.

    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 01-14-2014 at 03:24 PM. Reason: see note
    Reply With QuoteRegister To Reply
  5. 01-14-2014, 05:52 PM #5
    Zimmerray1
    Zimmerray1 is offline
    Registered User
    Join Date
    11-22-2013
    Location
    USA
    MS-Off Ver
    2003, 2007, 2010, Office 365
    Posts
    32

    Re: Convert text to number and change date format in a macro.

    Oops Jeff,

    You did exactly what I said I needed. And I have already used that today and works great. However, I gave you some bad info. If you would let me apologize, I would ask one more favor. I need columns "B" and "H" to be converted to numbers. They are already text to start with. If you would be so kind as to fix that for me, I would be forever grateful.


    By the way, I am a 5 stringer - bass

    Best regards!
    Last edited by Zimmerray1; 01-14-2014 at 06:41 PM.
    Reply With QuoteRegister To Reply
  6. 01-15-2014, 01:13 PM #6
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,995

    Re: Convert text to number and change date format in a macro.

    Looking at your example file, those columns are formatted as General and treated as numbers. I'm not sure why you describe them as text, or what you want to do with them that is not possible the way they are. However, you do want to remove my line of code that formats them as text:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 01-15-2014, 01:47 PM #7
    Kenneth Hobson
    Kenneth Hobson is offline
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Convert text to number and change date format in a macro. 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 01-15-2014, 02:48 PM #8
    Zimmerray1
    Zimmerray1 is offline
    Registered User
    Join Date
    11-22-2013
    Location
    USA
    MS-Off Ver
    2003, 2007, 2010, Office 365
    Posts
    32

    Re: Convert text to number and change date format in a macro.

    @ Jeff and Kenneth:

    Thanks for your help!

    When I added this...
    Please Login or Register  to view this content.
    ...to what Jeff had already done, I got the exact results I was looking for.

    I appreciate you guys! My daily tasks will go much more smoothly now.
    Last edited by Zimmerray1; 01-15-2014 at 07:01 PM.
    Reply With QuoteRegister To Reply
  9. 01-15-2014, 03:21 PM #9
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,995

    Re: Convert text to number and change date format in a macro.

    Zimmerray1, I'm glad you got your solution. Please add code tags to your most recent post; required by rules.

    [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE]
    Reply With QuoteRegister To Reply
  10. 01-15-2014, 07:04 PM #10
    Zimmerray1
    Zimmerray1 is offline
    Registered User
    Join Date
    11-22-2013
    Location
    USA
    MS-Off Ver
    2003, 2007, 2010, Office 365
    Posts
    32

    Re: Convert text to number and change date format in a macro.

    Thanks! I am already using the macro. Works like a dream. Incredible time saver when you consider how much data manipulation it is actually doing.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro to convert Text to Date format
    By akynyemi in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-17-2013, 12:18 PM
  2. Convert date and time in serial number format to text format
    By nda13112 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-11-2013, 02:45 PM
  3. Macro to convert cells to text or value based on date change
    By simpson in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-17-2012, 09:47 PM
  4. [SOLVED] Macro to change the format of text to date
    By kishoremcp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-10-2012, 09:57 AM
  5. Formulae to change text formatted date to number format
    By shekar goud in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-11-2010, 01:57 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1