Insert pipe symbol as first character in all cells of every column in sheet

[RogerC]

Is there a method to insert a pipe symbol into every cell of my spreadsheet that contains data, to act as a delimiter character?

 

[mumps]

Try:

Sub InsertPipe()
    Application.ScreenUpdating = False
    Dim v As Variant, r As Long, c As Long, arr() As Variant
    v = ActiveSheet.UsedRange.Cells.Value
    ReDim arr(1 To UBound(v), 1 To UBound(v, 2))
    For r = LBound(v) To UBound(v)
        For c = LBound(v, 2) To UBound(v, 2)
            If v(r, c) <> "" Then
                arr(r, c) = "|" & v(r, c)
            Else
                arr(r, c) = ""
            End If
        Next c
    Next r
    Range("A1").Resize(UBound(v), UBound(v, 2)) = arr
    Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

or just this?

Sub Insert_Pipe()
  With ActiveSheet.UsedRange
    .Value = Evaluate(Replace("if(#="""","""",""|""&#)", "#", .Address))
  End With
End Sub

 

[jolivanes]

@mumps
To your knowledge, is there a disadvantage to go with a single array?

Sub InsertPipe_2()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, ii As Long
    v = ActiveSheet.UsedRange.Value
    For i = LBound(v) To UBound(v)
        For ii = LBound(v, 2) To UBound(v, 2)
            If Len(v(i, ii)) <> 0 Then v(i, ii) = "|" & v(i, ii)
        Next ii
    Next i
    Range("A1").Resize(UBound(v), UBound(v, 2)) = v
    Application.ScreenUpdating = True
End Sub

 

[jolivanes]

I found the difference.
With 1,300,208 Cells (100,016 Rows x 13 Columns) of which 952,784 cells have data and 347,424 cells are empty, your suggestion is 0.2 seconds faster.
Every little bit counts.

 

[mumps]

@jolivanes
Thanks for your feedback.