Delete everything before the colon

Abhishekghorpade

Board Regular
Joined
Oct 3, 2018
Messages
78
Hi,

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

Thank you in advance.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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 _
    :="(", 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.
 
Upvote 0
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
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 _
    :="(", 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.
 
Upvote 0
Hi, in your 1st post you mentioned Colon (

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

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

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]
 
Last edited:
Upvote 0
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 _
    :=":", 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 ?
 
Upvote 0
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
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 _
    :=":", 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 ?
 
Upvote 0
Dhanywaad Bhau..

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

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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