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

 

[VBABEGINER]

Highly appreciate, if you could give me solution on this..

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?

 

[njimack]

Still waiting for you to provide a sample of data and desired output.

Highly appreciate, if you could give me solution on this..

 

[VBABEGINER]

I have given. See my post no. #10 . Col A contains data.

and Col B require the output as mentioned.
and Col C require the output in date format.

This is the only im trying to do..

Still waiting for you to provide a sample of data and desired output.

 

[njimack]

I don't think this can be done because there don't appear to be any consistent rules to your data.

 

[VBABEGINER]

That's the reason, now i will write a single single code to every type..That is the only solution..

If I require, help on any type..please revert and suggest. Thanks till here... A much appreciated for your reply.

I don't think this can be done because there don't appear to be any consistent rules to your data.

 

[VBABEGINER]

Hey Neil,
Could you please suggest on this..
It giving me Type mismatch error..bcoz variable b taken as String...how to fix this..

Sub test()
Dim i As Integer
Dim b As String
Dim space As Long
 
Application.ScreenUpdating = False
i = Range("B" & Rows.Count).End(xlUp).Row
For b = 3 To i
'b = Range("A3")
If b Like "[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


'b = Range("A4")
If b Like "[A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][A-Z][A-Z] ([0-9][0-9]/[0-9][0-9])" Then
    space = WorksheetFunction.Find(" ", b)
    Range("B4") = Left(b, space - 1)
    Range("C4") = Right(b, Len(b) - space)
End If


Next
Application.ScreenUpdating = True
End Sub

I don't think this can be done because there don't appear to be any consistent rules to your data.

 

[njimack]

Change:
For b = 3 to i
to:
For i = 3 to i

 

[VBABEGINER]

Apologies, but how come..i = 3 to i..?
and what about b variable..from where b will get the cell value..?

Change:
For b = 3 to i
to:
For i = 3 to i

 

[njimack]

My bad- try this...

Sub test()
Dim LastRow As Long
Dim i As Integer
Dim b As String
Dim space As Long
 
Application.ScreenUpdating = False
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 3 To LastRow
    b = Cells(i, 1)
    If b Like "[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
Next i
Application.ScreenUpdating = True
End Sub

 

[VBABEGINER]

Neil, Thanks for this...But I think yesterday while showing you actual data...We skip one problem..in post #9
i.e. In Col C, for below example..

UTS-COVPG 7-03

it is coming..

7/3/2018

Wherein, 7 is the Month and 03 is the Year.
Therefore, Col C require like this..

7/1/2003

..

Can we amend this..