Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Move Text To End Of Cell

  1. #1
    Board Regular
    Join Date
    Nov 2013
    Posts
    214
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Move Text To End Of Cell

    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

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    6,428
    Post Thanks / Like
    Mentioned
    66 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Move Text To End Of Cell

    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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,381
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move Text To End Of Cell

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

    Click below for a white paper on Data Base Design
    https://www.coursehero.com/file/8261...atabaseDesign/



  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    14,282
    Post Thanks / Like
    Mentioned
    259 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Move Text To End Of Cell

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  5. #5
    Board Regular
    Join Date
    Dec 2004
    Location
    Chester, IL
    Posts
    2,643
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move Text To End Of Cell

    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.
    Home: XL16 on Win07

    Work: XL13 on Win07

    Any answer provided is based on this(these) condition(s).

    ------------------------------------------------

    Post your excel WS to the board - HTMLMaker Add-In

    ------------------------------------------------

    Always test any code posted on a COPY of your data. Most changes, when performed by coding, are not able to be undone.

  6. #6
    Board Regular
    Join Date
    Nov 2013
    Posts
    214
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move Text To End Of Cell

    Quote Originally Posted by mumps View Post
    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.

  7. #7
    Board Regular
    Join Date
    Nov 2013
    Posts
    214
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move Text To End Of Cell

    Quote Originally Posted by jproffer View Post
    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.

  8. #8
    Board Regular
    Join Date
    Nov 2013
    Posts
    214
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move Text To End Of Cell

    Quote Originally Posted by Fluff View Post
    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.

  9. #9
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    6,428
    Post Thanks / Like
    Mentioned
    66 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Move Text To End Of Cell

    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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  10. #10
    Board Regular
    Join Date
    Nov 2013
    Posts
    214
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move Text To End Of Cell

    Quote Originally Posted by alansidman View Post
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •