Deleting text after a specific character

[dubabear]

Currently I'm working on a macro that will find "|" copy the row and paste it. This is where I'm getting stuck as I'm trying to delete the text after |, then go to the copied cell and delete the text prior to |

This is the current VBA code:


Macro1 Macro
Cells.Find(What:="|", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Select
ActiveCell.EntireRow.Select
Selection.Copy
Selection.Insert Shift:=xlDown
Range("N" & (ActiveCell.Row)).Select
Replace "|*", "", x1Part
End Sub


Overall my situation is this, I receive a file that has invoice # and sometimes the invoice cell(column N) has two numbers separated by |, to do further analyze I have copy the row paste it, and assign a row to one of the invoice. Then I need to divide the amount$ (column R) by 2.

Majority 90% of duplicates are only two, sometimes there are 3 numbers or 4 all separated by |.

Thanks for the help.

[SamT]

Sub SplitAtBar()
Dim Tmp As Variant
Dim Amt As Double
Dim Found As Range
Dim FirstFoundAddress As String
Dim i As Long

Set Found = Range("N:N").Find("|")

If Not Found Is Nothing Then
FirstFoundAddress = Found.Address

Do While Found.Address <> FirstFoundAddress
Tmp = Split(Found.Value, "|") ' Split string at Bar, Assign each part to Array Tmp
Amt = Found.Offset(, 4) 'Column R = Column N + 4

Found.EntireRow.Copy
For i = LBound(tmp) to UBound(Tmp) 'Default Lower index of Arrays is zero
Found.Row.Insert
Next i
Application.CutCopyMode = False 'Clear the Clipboard

For i = LBound(tmp) to UBound(Tmp)
Found.Offset(i,) = Trim(Tmp(i)) 'Trim leading and trailing spaces
Found.Offset(i, 4) = Amt / (Ublound(tmp) + 1)
Next i

Set Found = FindNext("|")
Loop

End Sub

You can find my mistake :devil2: