Sub text2colls()
With Range(Cells(2, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row, ActiveCell.Column))
.Value = ("=IFERROR(LEFT(RC[-1],SEARCH(""|"",SUBSTITUTE(RC[-1],""-"",""|"",2))-1),"""")")
.Value = .Value
End With
End SubIt worked great. I have one last thing If the data doesn't have "-" it wont show at all. I want it to be shown example 12-12 result 12 12 result 12 Is it possible ?You can just paste as a value:You might have to change the cell format to text though if any are read as dates (e.g. 1-2, 2-10)Code:Sub text2colls() With Range(Cells(2, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row, ActiveCell.Column)) .Value = ("=IFERROR(LEFT(RC[-1],SEARCH(""|"",SUBSTITUTE(RC[-1],""-"",""|"",2))-1),"""")") .Value = .Value End With End Sub
It worked great. I have one last thing If the data doesn't have "-" it wont show at all. I want it to be shown example 12-12 result 12 12 result 12 Is it possible ?
You can just paste as a value:
Code:Sub text2colls() With Range(Cells(2, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row, ActiveCell.Column)) .Value = ("=IFERROR(LEFT(RC[-1],SEARCH(""|"",SUBSTITUTE(RC[-1],""-"",""|"",2))-1),"""")") .Value = .Value End With End Sub
You might have to change the cell format to text though if any are read as dates (e.g. 1-2, 2-10)
Sub text2colls()
With Range(Cells(2, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row, ActiveCell.Column))
.Value = ("=IFERROR(LEFT(RC[-1],SEARCH(""|"",SUBSTITUTE(RC[-1],""-"",""|"",2))-1),RC[-1])")
.Value = .Value
End With
End SubSheetspread,
what should I add to every other macro I have to turn formulas into values
this ?
.Value = .Value
Yes (after a cell/range)
