vba code require to separate date from code

[VBABEGINER]

Hi All,

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

This are the values in Col A.

[TABLE="width: 150"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CGU 002 - 05/89[/TD]
[/TR]
[TR]
[TD]ECG 21 501 - 05/00[/TD]
[/TR]
[TR]
[TD]ECG 21 762 - 04/14[/TD]
[/TR]
[TR]
[TD]CG 21 73 - 01/00[/TD]
[/TR]
[TR]
[TD]CG 21 06 - 05/14[/TD]
[/TR]
[TR]
[TD]CG 21 35 - 10/01[/TD]
[/TR]
[TR]
[TD]CG 21 47 - 12/07[/TD]
[/TR]
[TR]
[TD]CG 21 54 - 01/96[/TD]
[/TR]
[TR]
[TD]CG 21 55 - 09/99[/TD]
[/TR]
[TR]
[TD]CG 21 86 - 12/04[/TD]
[/TR]
[TR]
[TD]CG 21 96 - 03/05[/TD]
[/TR]
[TR]
[TD]CG 22 34 - 04/13[/TD]
[/TR]
[TR]
[TD]CG 22 43 - 04/13[TABLE="width: 150"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CGU 002 - 05/89[/TD]
[/TR]
[TR]
[TD]ECG 21 501 - 05/00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[D3allamerican07]

Try:

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

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

 

[Rick Rothstein]

Here is another macro that you can try...

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

 

[VBABEGINER]

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)

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

 

[VBABEGINER]

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

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

 

[VBABEGINER]

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

Here is another macro that you can try...

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

 

[Rick Rothstein]

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.

 

[VBABEGINER]

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.

 

[VBABEGINER]

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

 

[Rick Rothstein]

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