I want to push a button and have all cells in the column be reduced in length...

[powerstjm]

I want to be able to push a button and run a for each ( or any ) loop through the whole column.....after pressing the button i want each cell to be changed to just the cost code number with first dash. for example....

01-005 contingency --> after pressing the button i would only want it to read --> 01-005

Not a complete beginner with VB but with excel i am
looking for something along the lines of --> 'for each row in column A convert the cell LEFT ( cell.text , 6 ) --> I may be way off but hopefully someone can give me some advice

.

 

[nvond04]

I am using something similar. There is a Text-to-column feature that will separate the information to multiple columns. You can set the first column to be the number (##-###). Might need to set a filler column for the "-" then the next column would be the description.

This should at least separate the number out.

Columns("A:A").Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(6, 1)), TrailingMinusNumbers:=True

 

[nvond04]

update on the coding... This will split it to 3 separate columns. Number will be alone. B column will have the "-" separator. C column will have the description.

Sub Macro1()

Columns("A:A").Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(9, 1)), TrailingMinusNumbers:=True

End Sub

 

[Fluff]

Another option

Sub Powerstjm()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .TextToColumns Range("A2"), xlFixedWidth, , , , , , , , , Array(Array(0, 1), Array(6, 9))
   End With
End Sub

 

[powerstjm]

THANK YOU GUYS/GIRLS

 

[Fluff]

Glad we could help & thanks for the feedback.