Duplicate phrases in one cell

[Randy]

Thanks to all the MVPs and other knowledgeable people on this group. I
hope to someday become half as proficient. I’m having some trouble at
the moment and wonder if anyone has a snippet of code or other
solution to this pesky little issue …

I have cell data, arranged in rows spanning a yet unknown number of
rows (but arranged entirely in column A), that each contain two
instances of a phrase. Each cell generally has a unique set of phrases
(as opposed to other cells, that is), but not so in all cases. There
could be an instance where for example A50 might have the same two
phrases as that in A2.

I would like to eliminate the second instance of the phrase in each
row cell (in the cells spanning Column A, that is). The difficulty
lies in that the first letter of the second instance of the phrase is
always joined with the last word in the first instance.

Three example cells (exactly as encountered in my list):

I General principlesI General principles
3 Miscellaneous3 Miscellaneous
a Between reviewa Between review

Does anyone have any idea as to what I might do in order to make the
following happen:

1) Discard one instance of the phrase in each row cell, and
2) Keep only one instance of the phrase found in each row cell, and
3) Paste the one instance to column B, alongside its corresponding
cell in column A.


Any suggestions are sincerely appreciated.

thanks,

Randy

 

[Mike H]

Randy,

Right click your sheet tab, view cod3e and paste the code below in and run it.

Sub stantial()
Dim MyRange, copyrange As Range
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
splitval = Left(c, 1)
c.Offset(, 1).Value = Mid(c.Value, InStr(2, c.Value, splitval))
c.Value = Left(c.Value, Len(c.Value) - Len(Mid(c.Value, InStr(2,
c.Value, splitval))))
Next
End Sub

Mike

 

[Patrick Molloy]

for information

Dim MyRange, copyrange As Range

is the same as
Dim MyRange as Variant
DIM copyrange As Range

you should have this
Dim MyRange As Range, copyrange As Range

or this
Dim MyRange as Range
DIM copyrange As Range

 

[Peter T]

I don't fully follow the objective but based solely on your examples, it
looks like all you need to do is compare the left half with the right half.
You could do that with a VBA (could be a UDF) or with a simple formula, eg

=IF(LEFT(A1,LEN(A1)/2)=RIGHT(A1,LEN(A1)/2),LEFT(A1,LEN(A1)/2),A1)

If the task is not as simple as your examples imply, give further examples
to illustrate what you need to cater for, and what you want as results.

Regards,
Peter T



Thanks to all the MVPs and other knowledgeable people on this group. I
hope to someday become half as proficient. I’m having some trouble at
the moment and wonder if anyone has a snippet of code or other
solution to this pesky little issue …

I have cell data, arranged in rows spanning a yet unknown number of
rows (but arranged entirely in column A), that each contain two
instances of a phrase. Each cell generally has a unique set of phrases
(as opposed to other cells, that is), but not so in all cases. There
could be an instance where for example A50 might have the same two
phrases as that in A2.

I would like to eliminate the second instance of the phrase in each
row cell (in the cells spanning Column A, that is). The difficulty
lies in that the first letter of the second instance of the phrase is
always joined with the last word in the first instance.

Three example cells (exactly as encountered in my list):

I General principlesI General principles
3 Miscellaneous3 Miscellaneous
a Between reviewa Between review

Does anyone have any idea as to what I might do in order to make the
following happen:

1) Discard one instance of the phrase in each row cell, and
2) Keep only one instance of the phrase found in each row cell, and
3) Paste the one instance to column B, alongside its corresponding
cell in column A.


Any suggestions are sincerely appreciated.

thanks,

Randy

 

[Ron Rosenfeld]

Thanks to all the MVPs and other knowledgeable people on this group. I
hope to someday become half as proficient. I’m having some trouble at
the moment and wonder if anyone has a snippet of code or other
solution to this pesky little issue …

I have cell data, arranged in rows spanning a yet unknown number of
rows (but arranged entirely in column A), that each contain two
instances of a phrase. Each cell generally has a unique set of phrases
(as opposed to other cells, that is), but not so in all cases. There
could be an instance where for example A50 might have the same two
phrases as that in A2.

I would like to eliminate the second instance of the phrase in each
row cell (in the cells spanning Column A, that is). The difficulty
lies in that the first letter of the second instance of the phrase is
always joined with the last word in the first instance.

Three example cells (exactly as encountered in my list):

I General principlesI General principles
3 Miscellaneous3 Miscellaneous
a Between reviewa Between review

Does anyone have any idea as to what I might do in order to make the
following happen:

1) Discard one instance of the phrase in each row cell, and
2) Keep only one instance of the phrase found in each row cell, and
3) Paste the one instance to column B, alongside its corresponding
cell in column A.


Any suggestions are sincerely appreciated.

thanks,

Randy

This can be done with a VBA macro.

As written, it requires that there be an exact duplicate. In other words, if
one of your strings has a trailing space (or any other character) it will not
be seen as having a duplicate (this can be easily changed, if you have more
specifications).

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.

====================================
Option Explicit
Sub RemDup()
Dim c As Range, rg As Range
Dim LastRow As Long
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^(.*)(\1)$"

'rg set to A1:last row containing data in col A
'could be set to other ranges

Set rg = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

For Each c In rg
Set mc = re.Execute(c.Value)
If mc.Count > 0 Then
c.Offset(0, 1).Value = mc(0).submatches(1)
Else
c.Offset(0, 1).Value = ""
End If
Next c
End Sub
===============================

--ron

 

[Ron Rosenfeld]

I don't fully follow the objective but based solely on your examples, it
looks like all you need to do is compare the left half with the right half.
You could do that with a VBA (could be a UDF) or with a simple formula, eg

=IF(LEFT(A1,LEN(A1)/2)=RIGHT(A1,LEN(A1)/2),LEFT(A1,LEN(A1)/2),A1)

Very simple, and neat.
--ron

 

[Randy]

Very simple, and neat.
--ron

Excellent solutions! I am happy to report that these work perfectly
for what I was wishing to do. I want to thank you guys very much for
the ingenuity.

 

[Peter T]

Very simple, and neat.
--ron

Why thank you Ron.

Yours is very clever

Regards,
Peter T

 

[Ron Rosenfeld]

Why thank you Ron.

Yours is very clever

Regards,
Peter T

Mine uses Regular Expressions, and conceptually is doing the same thing as
yours. But a formulaic expression is usually much faster.

The only advantage to mine might be easy to change if the OP's requirements are
a bit different than posted.
--ron

 

[Peter T]

Mine uses Regular Expressions, and conceptually is doing the same thing as
yours.
The only advantage to mine might be easy to change if the OP's
requirements are
a bit different than posted.

Would you care to explain how (if left-half = right-half)

re.Pattern = "^(.*)(\1)$"
mc = re.Execute(theString)

puts the left half in this
mc(0).submatches(1)

Regards,
Peter T

 

[Ron Rosenfeld]

Would you care to explain how (if left-half = right-half)

re.Pattern = "^(.*)(\1)$"
mc = re.Execute(theString)

puts the left half in this
mc(0).submatches(1)

Regards,
Peter T

I can try.

Actually, its the right half that's in mc(0).submatches(1), but since the right
and left halves are the same, it doesn't matter. The left half would be in
mc(0).submatches(0).

The pattern defines what is matched as follows:

------------------------
Paired phrases

^(.*)(\1)$

Assert position at the beginning of the string «^»
Match the regular expression below and capture its match into backreference
number 1 «(.*)»
Match any single character that is not a line break character «.*»
Between zero and unlimited times, as many times as possible, giving back
as needed (greedy) «*»
Match the regular expression below and capture its match into backreference
number 2 «(\1)»
Match the same text as most recently matched by capturing group number 1
«\1»
Assert position at the end of the string (or before the line break at the end
of the string, if any) «$»


Created with RegexBuddy
----------------------------

Since the pattern includes the beginning and end of the line, it will only
match if the left half = the right half.

Then it's just a matter of returning one, or the other.

See these references regarding Regular Expressions. They are from my
bookmarks; hopefully some of them are still good!

Regular Expressions
http://www.regular-expressions.info/reference.html
http://support.microsoft.com/default.aspx?scid=kb;en-us;818802&Product=vbb
http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx
http://msdn2.microsoft.com/en-us/library/ms974619.aspx
http://www.regex-guru.info/
--ron

 

[Peter T]

Thanks for that Ron, it helps.
One of these days I'll try and get to grips with it, might need to get into
a certain frame of mind first !

Regards,
Peter T