Move Text To End Of Cell

Nanogirl21

Active Member
Joined
Nov 19, 2013
Messages
330
Office Version
  1. 365
Platform
  1. Windows
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:
CURRENT
RESULTS WANTED
APPLES - HOLD 15
APPLES 15 / HOLD
ORANGES - HOLD 40
ORANGES 40 / HOLD
PINEAPPLE - HOLD 00
PINEAPPLE 00 / HOLD

<tbody>
</tbody>
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Try:
Code:
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
 
Upvote 0
Assume your data in Column A, then in Column B and copy down =LEFT(A2,FIND("-",A2)-2) &" " & RIGHT(A2,2) & " /HOLD"
 
Upvote 0
How about
Code:
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
 
Upvote 0
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.
 
Upvote 0
Try:
Code:
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.
 
Upvote 0
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.
 
Upvote 0
How about
Code:
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.
 
Upvote 0
Try:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top