vba code require to separate date from code

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,232
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

<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>

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..
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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:
Upvote 0
Here is another macro that you can try...
Code:
[table="width: 500"]
[tr]
	[td]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[/td]
[/tr]
[/table]
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:
Upvote 0
Hey Thank You so much..Sorry for late reply. This works awesome. something new learned...

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
 
Upvote 0
Hi Rick,

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


Here is another macro that you can try...
Code:
[TABLE="width: 500"]
<tbody>[TR]
[TD]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[/TD]
[/TR]
</tbody>[/TABLE]
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.
 
Upvote 0
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




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...




Here is another macro that you can try...
Code:
[TABLE="width: 500"]
<tbody>[TR]
[TD]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[/TD]
[/TR]
</tbody>[/TABLE]
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.
 
Upvote 0
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.
 
Upvote 0
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, " - ")

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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top