Sub replaceStringInCell()
'Source: https://powerspreadsheets.com/
'For further information: https://powerspreadsheets.com/excel-vba-replace-substitute/
'declare object variable to hold reference to cell you work with
Dim myCell As Range
'declare variables to hold parameters for string replacement (string to replace, replacement string, and number of replacements)
Dim myStringToReplace As String
Dim myReplacementString As String
Dim myNumberOfReplacements As Long
'identify cell you work with
Set myCell = ActiveSheet.Range("A1")
'specify parameters for string replacement (string to replace, replacement string, and number of replacements)
myStringToReplace = "com"
myReplacementString = "com/"
myNumberOfReplacements = 300
'replace string in cell you work with, and assign resulting string to Range.Value property of cell you work with
myCell.Value = Replace(Expression:=myCell.Value, Find:=myStringToReplace, Replace:=myReplacementString, Count:=myNumberOfReplacements)
Macro1
End Sub
Sub Macro1()
'
' Macro1 Macro
'
'
Range("A1").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
ConvertRangeToColumn
End Sub
Sub ConvertRangeToColumn()
'Updateby20131126
Dim Range1 As Range, Range2 As Range, Rng As Range
Dim rowIndex As Integer
'xTitleId = "KutoolsforExcel"
Set Range1 = ActiveSheet.Range("A1:XFD1")
Set Range2 = ActiveSheet.Range("A3")
rowIndex = 0
Application.ScreenUpdating = False
For Each Rng In Range1.Rows
Rng.Copy
Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
rowIndex = rowIndex + Rng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
