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

Thread: Finding text

  1. #1
    New Member
    Join Date
    Apr 2016
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Finding text

    Hi There.

    I am a bit stuck here.

    As below, I am trying to find a way to locate a section of an address and add a comma to it as below. This a very large list and the address are all over the place (the street is just an example) Any help would be amazing!

    to simplify this - find all instances of B2 in A:A and replace them with C2

    Original What I need to find What I need to do RESULT
    1TheStreetAccrington Accrington ,Accrington 1TheStreet,Accrington
    1TheStreetAcle Acle ,Acle 1TheStreet,Acle
    1TheStreetActon Acton ,Acton 1TheStreet,Acton
    1TheStreetAdlington Adlington ,Adlington 1TheStreet,Adlington
    1TheStreetAlcester Alcester ,Alcester 1TheStreet,Alcester
    1TheStreetAldeburgh Aldeburgh ,Aldeburgh 1TheStreet,Aldeburgh
    1TheStreetAldershot Aldershot ,Aldershot 1TheStreet,Aldershot


    Thanks in advnace!

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,002
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Finding text

    With these simple examples the formula below should work
    D2 copied down
    =SUBSTITUTE(A2,B2,C2)

    M.

  3. #3
    New Member
    Join Date
    Apr 2016
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding text

    Thanks for your reply. Unfortunately this formula does not do the trick as there will be multiple instances of column B found column A. Column A may have up to 500 addresses that have B2 in them and this is what I am struggling with.

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,002
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Finding text

    As i said, "with these simple examples..."
    To getting help you should provide more examples. I have to leave now, maybe someone else can help you.

    M.

  5. #5
    New Member
    Join Date
    Apr 2016
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding text

    APologies if i caused any offence. Below is more specific example.

    Original What I need to find What I need to do RESULT
    1TheStreetAccrington Accrington ,Accrington 1TheStreet,Accrington
    2TheStreetAccrington Acle ,Acle 2TheStreet,Accrington
    3TheStreetAccrington Acton ,Acton 3TheStreet,Accrington
    1TheStreetAdlington Adlington ,Adlington 1TheStreet,Adlington
    1TheStreetAlcester Alcester ,Alcester 1TheStreet,Alcester
    1TheStreetAldeburgh Aldeburgh ,Aldeburgh 1TheStreet,Aldeburgh
    1TheStreetAldeburgh Aldershot ,Aldershot 1TheStreet,Aldeburgh

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,653
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Finding text

    I think you are going to need VBA in order to do this.
    I am envisioning two lists:
    - a list of your original data
    - a list of all the replacements you need

    What I would like to know more about is the structure and location of your data. The more detail you give us, the more we can tailor the response to your specific structure (and the less modifications you will need to make yourself).
    - Are these two lists going to be on the same sheet? If not, will they be on different sheets in the same workbook (if so, what is the name of those sheets)?
    - What columns will these lists reside in?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Apr 2016
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding text

    I was thinking this would need to be a VBA project.

    You are correct. Basically, I would use Sheet1 to paste the original data and Ideally would like the output on Sheet2. They would be in the same workbook and for simplicity I would keep the oringinal data in Sheet1!A:A and the replacements in Sheet2!A:A.

    I use VBA alot but really for simple tasks, this one is teetering over my skill levels!

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,653
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Finding text

    So, you do not want it to update the Original Data where it is located, but make a copy of it and do the replacements there (so you have a copy of the original data too)?
    I ask because it is not necessary to do that, unless you want to. We can update the original data on Sheet1 without making a copy of the output on Sheet2.

    But you still have not answered where this list of all the values to replace will be found. Is that on a separate sheet, or the same sheet as the data?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,653
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Finding text

    I am going to be away from my computer for a while, but wanted to post what I have done before I go.
    This works on your original data layout, where the Original data is in column A, the values to find are in column B, and the values to replace with are in column C.
    It makes the updates in the original column (A) instead of adding a new column.
    Code:
    Sub MyReplace()
    
        Dim rngOrig As Range
        Dim rngFind As Range
        Dim cell As Range
        Dim fndRow As Long
        Dim prevRow As Long
        
        Application.ScreenUpdating = False
        
    '   Set range of data to make replacements in
        Set rngOrig = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        
    '   Set range where the values to find are located
        Set rngFind = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
        
    '   Loop through all replacement values
        For Each cell In rngFind
            fndRow = 0
            prevRow = 0
            On Error Resume Next
            rngOrig.Replace What:=cell, Replacement:=cell.Offset(0, 1), LookAt _
                :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
            On Error GoTo 0
        Next cell
        
        Application.ScreenUpdating = True
        
    End Sub
    However, if you wanted the new data to be in column D, that is a pretty easy change. Just copy the data from column A to D first, and then work off of column D, like this:
    Code:
    Sub MyReplace()
    
        Dim rngOrig As Range
        Dim rngFind As Range
        Dim cell As Range
        Dim fndRow As Long
        Dim prevRow As Long
        
        Application.ScreenUpdating = False
        
    '   Copy data from column A to column D
        Range("A:A").Copy Range("D1")
        
    '   Set range of data to make replacements in
        Set rngOrig = Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)
        
    '   Set range where the values to find are located
        Set rngFind = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
        
    '   Loop through all replacement values
        For Each cell In rngFind
            fndRow = 0
            prevRow = 0
            On Error Resume Next
            rngOrig.Replace What:=cell, Replacement:=cell.Offset(0, 1), LookAt _
                :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
            On Error GoTo 0
        Next cell
        
        Application.ScreenUpdating = True
        
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    Board Regular
    Join Date
    May 2013
    Posts
    1,634
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Finding text

    Try this:

    Sheet1

    ABCDE
    1OriginalWhat I need to findWhat I need to doRESULTResult
    21TheStreetAccringtonAccrington,Accrington1TheStreet,Accrington1TheStreet,Accrington
    32TheStreetAccringtonAcle,Acle2TheStreet,Accrington1TheStreet,Accrington
    43TheStreetAccringtonActon,Acton3TheStreet,Accrington1TheStreet,Accrington
    51TheStreetAdlingtonAdlington,Adlington1TheStreet,Adlington1TheStreet,Adlington
    61TheStreetAlcesterAlcester,Alcester1TheStreet,Alcester1TheStreet,Alcester
    71TheStreetAldeburghAldeburgh,Aldeburgh1TheStreet,Aldeburgh1TheStreet,Aldeburgh
    81TheStreetAldeburgAldershot,Aldershot1TheStreet,Aldeburgh1TheStreetAldeburg

    Spreadsheet Formulas
    CellFormula
    E2{=IFERROR(LOOKUP(1,-SEARCH(B$2:B$8,A2),SUBSTITUTE($A$2:$A$8,$B$2:$B$8,","&$B$2:$B$8)),A2)}
    E3{=IFERROR(LOOKUP(1,-SEARCH(B$2:B$8,A3),SUBSTITUTE($A$2:$A$8,$B$2:$B$8,","&$B$2:$B$8)),A3)}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

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
  •