asjmoron

Board Regular
Joined
Apr 26, 2016
Messages
98
Office Version
  1. 2016
Platform
  1. Windows
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

OriginalWhat I need to findWhat I need to doRESULT
1TheStreetAccringtonAccrington,Accrington1TheStreet,Accrington
1TheStreetAcleAcle,Acle1TheStreet,Acle
1TheStreetActonActon,Acton1TheStreet,Acton
1TheStreetAdlingtonAdlington,Adlington1TheStreet,Adlington
1TheStreetAlcesterAlcester,Alcester1TheStreet,Alcester
1TheStreetAldeburghAldeburgh,Aldeburgh1TheStreet,Aldeburgh
1TheStreetAldershotAldershot,Aldershot1TheStreet,Aldershot

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>


Thanks in advnace!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
With these simple examples the formula below should work
D2 copied down
=SUBSTITUTE(A2,B2,C2)

M.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
APologies if i caused any offence. Below is more specific example.

OriginalWhat I need to findWhat I need to doRESULT
1TheStreetAccringtonAccrington,Accrington1TheStreet,Accrington
2TheStreetAccringtonAcle,Acle2TheStreet,Accrington
3TheStreetAccringtonActon,Acton3TheStreet,Accrington
1TheStreetAdlingtonAdlington,Adlington1TheStreet,Adlington
1TheStreetAlcesterAlcester,Alcester1TheStreet,Alcester
1TheStreetAldeburghAldeburgh,Aldeburgh1TheStreet,Aldeburgh
1TheStreetAldeburghAldershot,Aldershot1TheStreet,Aldeburgh

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Try this:

Excel Workbook
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
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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