Thanks:  0
Likes:  0

1. ## 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

2. ## Re: Finding text

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

M.

3. ## 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. ## Re: Finding text

As i said, "with these simple examples..."

M.

5. ## 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. ## 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?

7. ## 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. ## 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?

9. ## 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.
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```

10. ## Re: Finding text

Try this:

Sheet1

 A B C D E 1 Original What I need to find What I need to do RESULT Result 2 1TheStreetAccrington Accrington ,Accrington 1TheStreet,Accrington 1TheStreet,Accrington 3 2TheStreetAccrington Acle ,Acle 2TheStreet,Accrington 1TheStreet,Accrington 4 3TheStreetAccrington Acton ,Acton 3TheStreet,Accrington 1TheStreet,Accrington 5 1TheStreetAdlington Adlington ,Adlington 1TheStreet,Adlington 1TheStreet,Adlington 6 1TheStreetAlcester Alcester ,Alcester 1TheStreet,Alcester 1TheStreet,Alcester 7 1TheStreetAldeburgh Aldeburgh ,Aldeburgh 1TheStreet,Aldeburgh 1TheStreet,Aldeburgh 8 1TheStreetAldeburg Aldershot ,Aldershot 1TheStreet,Aldeburgh 1TheStreetAldeburg

 Cell Formula 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