vba code require to separate date from code

Page 1 of 4 123 ... LastLast
Results 1 to 10 of 36

Thread: vba code require to separate date from code

  1. #1
    Board Regular
    Join Date
    Jun 2011
    Location
    Mumbai, Maharashtra,India.
    Posts
    675
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default vba code require to separate date from code

     
    Hi All,

    I need a vba code, where I can split range value in code and date.

    This are the values in Col A.
    CGU 002 - 05/89
    ECG 21 501 - 05/00
    ECG 21 762 - 04/14
    CG 21 73 - 01/00
    CG 21 06 - 05/14
    CG 21 35 - 10/01
    CG 21 47 - 12/07
    CG 21 54 - 01/96
    CG 21 55 - 09/99
    CG 21 86 - 12/04
    CG 21 96 - 03/05
    CG 22 34 - 04/13
    CG 22 43 - 04/13
    CGU 002 - 05/89
    ECG 21 501 - 05/00
    Here, "05/89" these are dates. I want, "CGU 002" these in A range and "05/89" these in B Col with addition of "05/01/89"..

    Can some one pls help..

  2. #2
    Board Regular
    Join Date
    Jul 2015
    Location
    Virginia
    Posts
    93
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba code require to separate date from code

    Try:

    This is saying there is no header row (if header, change i = 2)

    Code:
    Sub Test()
    
        For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
            myArray = Split(ActiveSheet.Range("A" & i).Value, " - ")
            myDate = myArray(UBound(myArray))
            myInfo = myArray(LBound(myArray))
                With ActiveSheet
                    .Range("A" & i).Value = myInfo
                    .Range("B" & i).Value = myDate
                    .Range("B" & i).NumberFormat = "m/d/yyyy"
                End With
        Next i
    
    End Sub
    Last edited by D3allamerican07; Dec 7th, 2017 at 10:52 AM.
    Bill Steel
    Systems Engineer

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    30,757
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    5 Thread(s)

    Default Re: vba code require to separate date from code

    Here is another macro that you can try...
    Code:
    Sub Test() Columns("A").Replace " - ", Chr(1), xlPart, , , , False, False Columns("A").Replace "/", "/01/", xlPart, , , , False, False Columns("A").TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1) Columns("B").NumberFormat = "mm/dd/yyyy" End Sub
    Note: This code will work even if your data has headers in Row 2 as long as the header text does not have a dash surrounded by spaces nor a forward slash in it.
    Last edited by Rick Rothstein; Dec 7th, 2017 at 11:24 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    Board Regular
    Join Date
    Jun 2011
    Location
    Mumbai, Maharashtra,India.
    Posts
    675
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba code require to separate date from code

    Hey Thank You so much..Sorry for late reply. This works awesome. something new learned...

    Quote Originally Posted by D3allamerican07 View Post
    Try:

    This is saying there is no header row (if header, change i = 2)

    Code:
    Sub Test()
    
        For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
            myArray = Split(ActiveSheet.Range("A" & i).Value, " - ")
            myDate = myArray(UBound(myArray))
            myInfo = myArray(LBound(myArray))
                With ActiveSheet
                    .Range("A" & i).Value = myInfo
                    .Range("B" & i).Value = myDate
                    .Range("B" & i).NumberFormat = "m/d/yyyy"
                End With
        Next i
    
    End Sub

  5. #5
    Board Regular
    Join Date
    Jun 2011
    Location
    Mumbai, Maharashtra,India.
    Posts
    675
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba code require to separate date from code

    Hi Rick,

    Thank You so much for your reply. This too works great..Fantabulastic..Thanks for share something new..


    Quote Originally Posted by Rick Rothstein View Post
    Here is another macro that you can try...
    Code:
    Sub Test() Columns("A").Replace " - ", Chr(1), xlPart, , , , False, False Columns("A").Replace "/", "/01/", xlPart, , , , False, False Columns("A").TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1) Columns("B").NumberFormat = "mm/dd/yyyy" End Sub
    Note: This code will work even if your data has headers in Row 2 as long as the header text does not have a dash surrounded by spaces nor a forward slash in it.

  6. #6
    Board Regular
    Join Date
    Jun 2011
    Location
    Mumbai, Maharashtra,India.
    Posts
    675
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba code require to separate date from code

    Hi Rick and D3...

    Regarding to my query..how can I use multiple condition's in this...

    Some sample Im posting here..
    The below one are get's covered..
    CGU 002 - 05/89
    Other types..
    Here, "Ed." will be the identification.
    LIA-7139 Ed. 01-09
    The most challenging are,
    IL T8 01 10 93

    IL T8 25
    MCS 90
    ACF-7007 08/11
    ILT8011093
    MM99561013
    This all are fixed formats...Can you please help, how do I embed this format in our code, please...




    Quote Originally Posted by Rick Rothstein View Post
    Here is another macro that you can try...
    Code:
    Sub Test() Columns("A").Replace " - ", Chr(1), xlPart, , , , False, False Columns("A").Replace "/", "/01/", xlPart, , , , False, False Columns("A").TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1) Columns("B").NumberFormat = "mm/dd/yyyy" End Sub
    Note: This code will work even if your data has headers in Row 2 as long as the header text does not have a dash surrounded by spaces nor a forward slash in it.

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    30,757
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    5 Thread(s)

    Default Re: vba code require to separate date from code

    Quote Originally Posted by VBABEGINER View Post
    Hi Rick and D3...

    Regarding to my query..how can I use multiple condition's in this...

    Some sample Im posting here..
    The below one are get's covered..

    Other types..
    Here, "Ed." will be the identification.

    The most challenging are,
    Assuming those challenging values are located in Column A, please post what you need from each of those challenging values; that is, what goes in Column B and what goes in Column C.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Board Regular
    Join Date
    Jun 2011
    Location
    Mumbai, Maharashtra,India.
    Posts
    675
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba code require to separate date from code

    Hey Rick,
    Apologies, if I made some mistakes in my words... This things are challenging for me..bcoz i really dont know how to use this..

    NO, this all values are located in Col B.

    As we have done for one of the type i.e. "-" I want to do the same code for the types which I have mentioned..

    what I feel is, I need to place OR in this code line..You please correct..
    myArray = Split(ActiveSheet.Range("A" & i).Value, " - ")
    Quote Originally Posted by Rick Rothstein View Post
    Assuming those challenging values are located in Column A, please post what you need from each of those challenging values; that is, what goes in Column B and what goes in Column C.

  9. #9
    Board Regular
    Join Date
    Jun 2011
    Location
    Mumbai, Maharashtra,India.
    Posts
    675
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba code require to separate date from code

    Yeah, sorry...forgot to answer..

    In all the pattern, last 04 digit's are my date. (MM/YY format.) I want this last 04 digits in Col C, as this format..MM/01/YYYY
    and the remaining part are my application numbers...

    can we do this

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    30,757
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    5 Thread(s)

    Default Re: vba code require to separate date from code

      
    Quote Originally Posted by VBABEGINER View Post
    Yeah, sorry...forgot to answer..

    In all the pattern, last 04 digit's are my date. (MM/YY format.)
    The last 4 digits? What are the last 4 digits for these two examples that you posted in Message #6 ...

    IL T8 25

    MCS 90
    Last edited by Rick Rothstein; Dec 15th, 2017 at 12:44 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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
  •  

 

 
DMCA.com