vb code require, to understand format in cell value

[VBABEGINER]

Hi All,
Could anyone help me in code..

my cell a3 contains this format

ABC-ABCDEF M-DD

M=month
DD=date

how do i write vb code,

if range("A3").format = ABC-ABCDEF M-DD then, code..

 

[njimack]

If Range("A3") Like "[A-Z][A-Z][A-Z]-[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z] #-##" Then

(Note: Excel won't treat the last few characters as dates because they're part of a text string)

 

[VBABEGINER]

This really helps to build some logic at least...Thanks for this..
I may have again question on this..bcoz this is I used for single cell, I have 1000 of records, where i have to check this criteria..

Thanks, Neil

If Range("A3") Like "[A-Z][A-Z][A-Z]-[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z] #-##" Then

(Note: Excel won't treat the last few characters as dates because they're part of a text string)

 

[VBABEGINER]

Sub test()
Dim b As String
If Range("A3").Value Like "[A-Z][A-Z][A-Z]-[A-Z][A-Z][A-Z][A-Z][A-Z] #-##" Then
b = Range("A3") - Right(Range("A3"), 4)
Range("B3").Value = Range("A3") - Right(Range("A3"), 4)
Range("A3").Interior.Color = vbYellow
End If
End Sub

can you help me in this one..
this part i want in B3

[A-Z][A-Z][A-Z]-[A-Z][A-Z][A-Z][A-Z][A-Z]

this part i want in C3

#-##

but in this format.. #/01/##
#=Month and ## = Year

This really helps to build some logic at least...Thanks for this..
I may have again question on this..bcoz this is I used for single cell, I have 1000 of records, where i have to check this criteria..

Thanks, Neil

 

[VBABEGINER]

Any one on this...

Sub test()
Dim b As String
If Range("A3").Value Like "[A-Z][A-Z][A-Z]-[A-Z][A-Z][A-Z][A-Z][A-Z] #-##" Then
b = Range("A3") - Right(Range("A3"), 4)
Range("B3").Value = Range("A3") - Right(Range("A3"), 4)
Range("A3").Interior.Color = vbYellow
End If
End Sub

can you help me in this one..
this part i want in B3


this part i want in C3

but in this format.. #/01/##
#=Month and ## = Year

 

[njimack]

This should do the trick...

Sub test()
Dim b As String
Dim space As Long
 
Application.ScreenUpdating = False
 
b = Range("A3")
If b Like "[A-Z][A-Z][A-Z]-[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z] #-##" Then
    space = WorksheetFunction.Find(" ", b)
    Range("B3") = Left(b, space - 1)
    Range("C3") = Right(b, Len(b) - space)
End If
 
Application.ScreenUpdating = True
 
End Sub

 

[VBABEGINER]

Woww..!!

Neil, hold on..

In this example..C3 output generating like this..

7/3/2018

This is not correct.
B'coz, here in example..7 is the Month and 03 is Year..For C3 output should like,

7/1/2003

This should do the trick...

Sub test()
Dim b As String
Dim space As Long
 
Application.ScreenUpdating = False
 
b = Range("A3")
If b Like "[A-Z][A-Z][A-Z]-[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z] #-##" Then
    space = WorksheetFunction.Find(" ", b)
    Range("B3") = Left(b, space - 1)
    Range("C3") = Right(b, Len(b) - space)
End If
 
Application.ScreenUpdating = True
 
End Sub

 

[njimack]

You said the string contained M-DD, you didn't mention Year.

Can you post a small sample of your actual data and desired output?

 

[VBABEGINER]

my apologies..
its not M-DD, it is M-YY and output will like, M-01-YY
Yes sure..

Before, giving sample data, i just want to tell you that, I dont have few examples like this..Im stucked in problem..im finding one-one solution..

You said the string contained M-DD, you didn't mention Year.

Can you post a small sample of your actual data and desired output?

 

[VBABEGINER]

Hey Neil,
See this are the only examples...For one one im trying..b'coz even i dont understand how do to separation by using coding..

[TABLE="width: 399"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]Output should be..[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Output Col B[/TD]
[TD]Output Col C[/TD]
[/TR]
[TR]
[TD]UTS-COVPG  7-03[/TD]
[TD]UTS-COVPG[/TD]
[TD="align: right"]7/1/2003[/TD]
[/TR]
[TR]
[TD]EP 1051-CA (07 13)[/TD]
[TD]EP 1051-CA[/TD]
[TD="align: right"]7/1/2013[/TD]
[/TR]
[TR]
[TD]17-02-5205 7-03[/TD]
[TD]17-02-5205[/TD]
[TD="align: right"]7/1/2003[/TD]
[/TR]
[TR]
[TD]17-02-5205 2-98[/TD]
[TD]17-02-5205[/TD]
[TD="align: right"]2/1/1998[/TD]
[/TR]
[TR]
[TD]17-02-52 - 01/57[/TD]
[TD]17-02-52[/TD]
[TD="align: right"]1/1/1957[/TD]
[/TR]
[TR]
[TD]ABCDEF 02 - 01/20[/TD]
[TD]ABCDEF 02[/TD]
[TD="align: right"]1/1/2020[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NOTS0021CA (09/03)[/TD]
[TD]NOTS0021CA[/TD]
[TD="align: right"]9/1/2003[/TD]
[/TR]
[TR]
[TD]11-02-1220 (09/03)[/TD]
[TD]11-02-1220[/TD]
[TD="align: right"]9/1/2003[/TD]
[/TR]
[TR]
[TD]AXIS 102 A/C (06 15)[/TD]
[TD]AXIS 102 A/C[/TD]
[TD="align: right"]6/1/2015[/TD]
[/TR]
[TR]
[TD]CC-1k11H (03/14)[/TD]
[TD]CC-1k11H[/TD]
[TD="align: right"]3/1/2014[/TD]
[/TR]
[TR]
[TD]XSC-27266 (05/09)[/TD]
[TD]XSC-27266[/TD]
[TD="align: right"]5/1/2009[/TD]
[/TR]
[TR]
[TD]EP 4104 (06 13)[/TD]
[TD]EP 4104[/TD]
[TD="align: right"]6/1/2013[/TD]
[/TR]
[TR]
[TD]EPHG (06 13)[/TD]
[TD]EPHG[/TD]
[TD="align: right"]6/1/2013[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IL 00 17 02/2013[/TD]
[TD]IL 00 17[/TD]
[TD="align: right"]2/1/2013[/TD]
[/TR]
[TR]
[TD]IL P 001 01/04[/TD]
[TD]IL P 001[/TD]
[TD="align: right"]1/1/2004[/TD]
[/TR]
[TR]
[TD]RHIC 6000 08/11[/TD]
[TD]RHIC 6000[/TD]
[TD="align: right"]8/1/2011[/TD]
[/TR]
[TR]
[TD]05 PCD0071 00 03 10[/TD]
[TD]05 PCD0071 00[/TD]
[TD="align: right"]3/1/2010[/TD]
[/TR]
[TR]
[TD]00 ML0012 00 09 04[/TD]
[TD]00 ML0012 00[/TD]
[TD="align: right"]9/1/2004[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10-02-2543 (10/16 ed.)[/TD]
[TD]10-02-2543[/TD]
[TD="align: right"]10/1/2016[/TD]
[/TR]
[TR]
[TD]14-02-1350 (1/95 ed.)[/TD]
[TD]14-02-1350[/TD]
[TD="align: right"]1/1/1995[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EPP-E122-0116[/TD]
[TD]EPP-E122[/TD]
[TD="align: right"]1/1/2016[/TD]
[/TR]
[TR]
[TD]EPP-POL-0410[/TD]
[TD]EPP-POL[/TD]
[TD="align: right"]4/1/2010[/TD]
[/TR]
[TR]
[TD]EXL 0001 0615[/TD]
[TD]EXL 0001[/TD]
[TD="align: right"]6/1/2015[/TD]
[/TR]
[TR]
[TD]HC00051212[/TD]
[TD]HC0005[/TD]
[TD="align: right"]12/1/2012[/TD]
[/TR]
[TR]
[TD]HC70010605[/TD]
[TD]HC7001[/TD]
[TD="align: right"]6/1/2005[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]95863.2857142837[/TD]
[TD]95863.2857142837[/TD]
[TD]NO CHANGE[/TD]
[/TR]
</tbody>[/TABLE]

You said the string contained M-DD, you didn't mention Year.

Can you post a small sample of your actual data and desired output?