If contains

[lightbulb]

This is kinda hard to explain, but I'll do the best I can to try. I want a
formula that does the following...

I want to search column G, for data that is in column E. Anytime there's
data found in column G that is in column E, I want it to be replaced with the
information from column F. The trick is that there may be information before
or after the data from column G that's being looked up from column E. For
example, if in E2 I have X-129.9Y-135.94 and in G4 I have
G98G73X-129.9Y-135.94Z5.5R5.Q2.F50. and F2 has 129.9Y-135.94(E-25)...I want
to the part of the data in G4 that matches E2, with the data in F2.

I could simply do a find and replace, but there are hundreds in the file, so
it would take forever and I was wondering if there was a macro or a formula
that I could use instead.

Any help?

 

[JLatham]

Try this code. Not the warning to test it out in a COPY of your original
work just in case I got it all wrong. Since it does change things, if it
doesn't work properly, your data could be hopelessly corrupted if it doesn't
do what you want.

To put the code in your workbook: Open your workbook. Use File Save As to
save it with a new name, thus preserving your original work. Press
[Alt]+[F11] to open the VB Editor. Use Insert --> Module in the VBE and copy
the code below and paste it into the module. Close the VB Editor. Save the
workbook.

Select the sheet with the data to be modified, use Tools --> Macro --->
Macros to select and run the code.

Sub ReplaceG_From_E()
'****************************************
'TEST THIS IN A COPY of your workbook!!
'This process is 'destructive' so if it
'does not work properly, your original
'data may become hopelessly corrupted
'****************************************
'select the sheet with the data to be
'examined before running this macro
Dim sRange As Range 'Column E
Dim anySEntry As Range 'cell in E
Dim rRange As Range ' column G
Dim anyREntry ' cell in G
Dim EValue As String
Dim FValue As String
Dim GValue As String
Const offset2F = 1 ' from E to F

Set sRange = ActiveSheet.Range("E2:" & _
ActiveSheet.Range("E" & Rows.Count).End(xlUp).Address)
Set rRange = ActiveSheet.Range("G2:" & _
ActiveSheet.Range("G" & Rows.Count).End(xlUp).Address)

Application.ScreenUpdating = False ' improve performance
For Each anyREntry In rRange
'look at each cell in column G
'see if contents of a cell in Column E
'is within the cell in column G
GValue = Trim(anyREntry.Value)
For Each anySEntry In sRange
EValue = Trim(anySEntry.Value)
' MsgBox "Looking For: '" & anySEntry & "'" & vbCrLf & "In: '" &
anyREntry & "'"
If InStr(GValue, EValue) > 0 Then
FValue = Trim(anySEntry.Offset(0, offset2F).Value)
'replace the part of G matching E
'with F on same row as E
anyREntry.Value = Replace(GValue, EValue, FValue)
End If
Next
Next
Set sRange = Nothing
Set rRange = Nothing
MsgBox "Replacements Completed"
End Sub

Hope this helps.

 

[Ron Rosenfeld]

This is kinda hard to explain, but I'll do the best I can to try. I want a
formula that does the following...

I want to search column G, for data that is in column E. Anytime there's
data found in column G that is in column E, I want it to be replaced with the
information from column F. The trick is that there may be information before
or after the data from column G that's being looked up from column E. For
example, if in E2 I have X-129.9Y-135.94 and in G4 I have
G98G73X-129.9Y-135.94Z5.5R5.Q2.F50. and F2 has 129.9Y-135.94(E-25)...I want
to the part of the data in G4 that matches E2, with the data in F2.

I could simply do a find and replace, but there are hundreds in the file, so
it would take forever and I was wondering if there was a macro or a formula
that I could use instead.

Any help?

This solution assumes that there are no blanks in column G.

In the formula, "rng" refers to the range in column G where the data exists:
e.g. E2:E1000, or whatever.

The formula can be entered in, for example, column H, adjacent to the first
entry in Column G that you need to replace. Then fill down.


This formula must be **array-entered**:

=IF(ISNA(REPLACE(G2,MAX(IF(ISNUMBER(FIND(rng,G2)),
FIND(rng,G2),0)),LEN(INDEX(rng,MATCH(TRUE,
ISNUMBER(FIND(rng,G2)),0))),INDEX(OFFSET(rng,0,1),
MATCH(TRUE,ISNUMBER(FIND(rng,G2)),0)))),G2,
REPLACE(G2,MAX(IF(ISNUMBER(FIND(rng,G2)),
FIND(rng,G2),0)),LEN(INDEX(rng,MATCH(TRUE,
ISNUMBER(FIND(rng,G2)),0))),INDEX(OFFSET(rng,0,1),
MATCH(TRUE,ISNUMBER(FIND(rng,G2)),0))))
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

If you want to replace the original data:
Select the range within Column H that has the results
Edit/Copy
select the first cell in column G (e.g. G2)
Edit/Paste Special/Values
--ron

 

[lightbulb]

I tried the macro, and it said there was an error in the macro where it has
..... MsgBox "Looking For: '" & anySEntry & "'" & vbCrLf & "In: '" &
anyREntry & "'" ... it had the part that says anyREntry&"" in red, so I
deleted that portion of the macro and it worked fine...does this do any
damage to delete this part of the macro?

Thanks!

Try this code. Not the warning to test it out in a COPY of your original
work just in case I got it all wrong. Since it does change things, if it
doesn't work properly, your data could be hopelessly corrupted if it doesn't
do what you want.

To put the code in your workbook: Open your workbook. Use File Save As to
save it with a new name, thus preserving your original work. Press
[Alt]+[F11] to open the VB Editor. Use Insert --> Module in the VBE and copy
the code below and paste it into the module. Close the VB Editor. Save the
workbook.

Select the sheet with the data to be modified, use Tools --> Macro --->
Macros to select and run the code.

Sub ReplaceG_From_E()
'****************************************
'TEST THIS IN A COPY of your workbook!!
'This process is 'destructive' so if it
'does not work properly, your original
'data may become hopelessly corrupted
'****************************************
'select the sheet with the data to be
'examined before running this macro
Dim sRange As Range 'Column E
Dim anySEntry As Range 'cell in E
Dim rRange As Range ' column G
Dim anyREntry ' cell in G
Dim EValue As String
Dim FValue As String
Dim GValue As String
Const offset2F = 1 ' from E to F

Set sRange = ActiveSheet.Range("E2:" & _
ActiveSheet.Range("E" & Rows.Count).End(xlUp).Address)
Set rRange = ActiveSheet.Range("G2:" & _
ActiveSheet.Range("G" & Rows.Count).End(xlUp).Address)

Application.ScreenUpdating = False ' improve performance
For Each anyREntry In rRange
'look at each cell in column G
'see if contents of a cell in Column E
'is within the cell in column G
GValue = Trim(anyREntry.Value)
For Each anySEntry In sRange
EValue = Trim(anySEntry.Value)
' MsgBox "Looking For: '" & anySEntry & "'" & vbCrLf & "In: '" &
anyREntry & "'"
If InStr(GValue, EValue) > 0 Then
FValue = Trim(anySEntry.Offset(0, offset2F).Value)
'replace the part of G matching E
'with F on same row as E
anyREntry.Value = Replace(GValue, EValue, FValue)
End If
Next
Next
Set sRange = Nothing
Set rRange = Nothing
MsgBox "Replacements Completed"
End Sub

Hope this helps.

This is kinda hard to explain, but I'll do the best I can to try. I want a
formula that does the following...

I want to search column G, for data that is in column E. Anytime there's
data found in column G that is in column E, I want it to be replaced with the
information from column F. The trick is that there may be information before
or after the data from column G that's being looked up from column E. For
example, if in E2 I have X-129.9Y-135.94 and in G4 I have
G98G73X-129.9Y-135.94Z5.5R5.Q2.F50. and F2 has 129.9Y-135.94(E-25)...I want
to the part of the data in G4 that matches E2, with the data in F2.

I could simply do a find and replace, but there are hundreds in the file, so
it would take forever and I was wondering if there was a macro or a formula
that I could use instead.

Any help?