Delete everything before the colon

[Abhishekghorpade]

Hi,

Please give the macro code to delete everything before the Colon ) in column B.

Thank you in advance.

 

[VBABEGINER]

Sub Macro1()
Columns("C:C").Select
Selection.Insert Shift:=xlToRight


Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="(", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True


Range("B1").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("B1").Select
End Sub

copy above code in module (Alt+F11).
If you dont know what is module, then in left hand side you can see Project Explorer window. Select your excel workbook - Microsoft Excel Objects - Right on click on this - Insert - MOdule - paste the code - After keep the cursor on "Sub Macro1() - Click F5.

 

[Abhishekghorpade]

It delete everything in column B. text something looks like this - HI00339E:Investment AA inactiv, I want to delete only things which are there before colon

Sub Macro1()
Columns("C:C").Select
Selection.Insert Shift:=xlToRight


Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="(", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True


Range("B1").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("B1").Select
End Sub

copy above code in module (Alt+F11).
If you dont know what is module, then in left hand side you can see Project Explorer window. Select your excel workbook - Microsoft Excel Objects - Right on click on this - Insert - MOdule - paste the code - After keep the cursor on "Sub Macro1() - Click F5.

 

[VBABEGINER]

Hi, in your 1st post you mentioned Colon (

So it look like Colon you have mentioned mistakenly. No worries, Try this code..

Sub Macro1()
Columns("C:C").Select
Selection.Insert Shift:=xlToRight


Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="[COLOR=#ff0000]:[/COLOR]", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True


Range("B1").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("B1").Select
End Sub

 

[Abhishekghorpade]

Its working perfect if i ran it separately.. If i include it with my its clearing everything.. Is it bcoz I have Below code for column C ??

Sub ClearText()
Columns("C").SpecialCells(xlConstants, xlTextValues).ClearContents
End Sub

So it look like Colon you have mentioned mistakenly. No worries, Try this code..

Sub Macro1()
Columns("C:C").Select
Selection.Insert Shift:=xlToRight


Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="[COLOR=#ff0000]:[/COLOR]", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True


Range("B1").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("B1").Select
End Sub

[/QUOTE]

 

[VBABEGINER]

Try this

Sub Macro1()
Columns("C:C").Select
Selection.Insert Shift:=xlToRight


Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True


Range("B1").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("B1").Select
End Sub

[COLOR=#333333]Columns("C").SpecialCells(xlConstants, xlTextValues).ClearContents

[/COLOR]

Are you manually running this code ?

 

[Abhishekghorpade]

I am really sorry there was a problem in my coding..
Your is working perfect.. Thank you so much for your time and effort

Try this

Sub Macro1()
Columns("C:C").Select
Selection.Insert Shift:=xlToRight


Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True


Range("B1").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("B1").Select
End Sub

[COLOR=#333333]Columns("C").SpecialCells(xlConstants, xlTextValues).ClearContents

[/COLOR]

Are you manually running this code ?

 

[VBABEGINER]

Dhanywaad Bhau..

Wait for some more time, You will defiantly get some expert solution here from MrExcel board experts.. This code is very basic.