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
    188
    Post Thanks / Like
    Mentioned
    1 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
    5,729
    Post Thanks / Like
    Mentioned
    45 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,166
    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
    9,196
    Post Thanks / Like
    Mentioned
    164 Post(s)
    Tagged
    10 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 guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  5. #5
    Board Regular
    Join Date
    Dec 2004
    Posts
    2,442
    Post Thanks / Like
    Mentioned
    6 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: Unfortunately, no Excel at home at this time

    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
    188
    Post Thanks / Like
    Mentioned
    1 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
    188
    Post Thanks / Like
    Mentioned
    1 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
    188
    Post Thanks / Like
    Mentioned
    1 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
    5,729
    Post Thanks / Like
    Mentioned
    45 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
    188
    Post Thanks / Like
    Mentioned
    1 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.

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
  •  

 

DMCA.com