Move Text To End Of Cell

[Nanogirl21]

I have text in columns T, U, and V on a sheet called Tracking. In some of the cells I have “- Hold “. I would like to delete “- Hold “ and add “/ Hold“ to the end of the remaining text in the cell. Is there any way to do this using VBA? If not, what about a standard formula?

EXAMPLE:
[TABLE="width: 500"]
<tbody>[TR]
[TD]CURRENT
[/TD]
[TD]RESULTS WANTED
[/TD]
[/TR]
[TR]
[TD]APPLES - HOLD 15
[/TD]
[TD]APPLES 15 / HOLD
[/TD]
[/TR]
[TR]
[TD]ORANGES - HOLD 40
[/TD]
[TD]ORANGES 40 / HOLD
[/TD]
[/TR]
[TR]
[TD]PINEAPPLE - HOLD 00
[/TD]
[TD]PINEAPPLE 00 / HOLD
[/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

Try:

Sub test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("T2:V" & LastRow)
        rng = WorksheetFunction.Substitute(rng, "- HOLD", "") & " / HOLD"
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[alansidman]

Assume your data in Column A, then in Column B and copy down =LEFT(A2,FIND("-",A2)-2) &" " & RIGHT(A2,2) & " /HOLD"

 

[Fluff]

How about

Sub MoveHold()
   With Range("T1", Range("V" & Rows.count).End(xlUp))
      .Value = Evaluate(Replace("=IF(ISERROR(FIND("" - HOLD"",@,1)),@,SUBSTITUTE(@,"" - HOLD"","""")& "" / HOLD"")", "@", .Address))
   End With
End Sub

 

[jproffer]

Or, to add to the mix here

=IF(ISERROR(FIND("HOLD",A1)),LEFT(A1,FIND("-",A1)-2)&" "&RIGHT(A1,LEN(A1)-FIND("-",A1)-1),LEFT(A1,FIND("-",A1)-2)&" "&RIGHT(A1,LEN(A1)-FIND("-",A1)-6)&" / HOLD")

Assuming a space before and after the "-" and assuming if you don't find "HOLD" you want to see "APPLES 15", etc.

Just another way to accomplish the same thing.

 

[Nanogirl21]

Try:

Sub test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("T2:V" & LastRow)
        rng = WorksheetFunction.Substitute(rng, "- HOLD", "") & " / HOLD"
    Next rng
    Application.ScreenUpdating = True
End Sub

I tried this code, but it is adding / HOLD to the end of every cell. I only want to add the / HOLD to the cells that contact - HOLD. Not all cells will have - HOLD.

Also, this code is cycling through each cell individually. My data is 15,330 rows and growing daily. Would take a super long time to move to each cell 1 by 1.

 

[Nanogirl21]

Or, to add to the mix here

=IF(ISERROR(FIND("HOLD",A1)),LEFT(A1,FIND("-",A1)-2)&" "&RIGHT(A1,LEN(A1)-FIND("-",A1)-1),LEFT(A1,FIND("-",A1)-2)&" "&RIGHT(A1,LEN(A1)-FIND("-",A1)-6)&" / HOLD")

Assuming a space before and after the "-" and assuming if you don't find "HOLD" you want to see "APPLES 15", etc.

Just another way to accomplish the same thing.

Thank you. I tried this, but it is only deleting the -. It is not moving HOLD to the end or adding the /. Also, returns a value error if the cell does not have - HOLD.

 

[Nanogirl21]

How about

Sub MoveHold()
   With Range("T1", Range("V" & Rows.count).End(xlUp))
      .Value = Evaluate(Replace("=IF(ISERROR(FIND("" - HOLD"",@,1)),@,SUBSTITUTE(@,"" - HOLD"","""")& "" / HOLD"")", "@", .Address))
   End With
End Sub

I tried this code and nothing happen at all.

 

[mumps]

Try:

Sub test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("T2:T" & LastRow)
        If InStr(1, rng, "HOLD") > 0 Then
            rng = WorksheetFunction.Substitute(rng, "- HOLD", "") & " / HOLD"
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Nanogirl21]

Assume your data in Column A, then in Column B and copy down =LEFT(A2,FIND("-",A2)-2) &" " & RIGHT(A2,2) & " /HOLD"

This worked. Thank you.