Removing Duplicate Words in a single Cell

[eesterkin]

Hi,

I've got the following product dimensions in a single cell:

[TABLE="width: 889"]
<tbody>[TR]
[TD="width: 889"]Small: 9'' H x Small: 3'' Dia x Medium: 9.5'' H x Medium: 4.25'' Dia x Large: 12'' H x Large: 4.25'' Dia

My goal is to only have the word Small listed once, Medium Once, Large Once...so it would look like
[/TD]
[/TR]
</tbody>[/TABLE]
Small: 9'' H x 3'' Dia x Medium: 9.5'' H x 4.25'' Dia x Large: 12'' H x 4.25'' Dia

I've tried using the following VBA:

Function RemoveDupes2(txt As String, Optional delim As String = " ") As String
Dim x
'Updateby20140924
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each x In Split(txt, delim)
If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
Next
If .Count > 0 Then RemoveDupes2 = Join(.keys, delim)
End With
End Function

Problem is that it removes the "Dia" and other words since they are duplicate word as well.

Can anyone help me specify which duplicate words I want to remove using a formula or VBA?

Thank you!

 

[JoeMo]

Does this give you the result you want?

Excel Workbook
	A	B
1	Small: 9'' H x Small: 3'' Dia x Medium: 9.5'' H x Medium: 4.25'' Dia x Large: 12'' H x Large: 4.25'' Dia	Small: 9'' H x 3'' Dia x Medium: 9.5'' H x 4.25'' Dia x Large: 12'' H x 4.25'' Dia
Sheet4

 

[eesterkin]

Thank you so much! It does give me the value I requested...now I'm seeing that I made an error...rather than

Small: 9'' H x 3'' Dia x Medium: 9.5'' H x 4.25'' Dia x Large: 12'' H x 4.25'' Dia

I need it to look like

Small: 9'' H x 3'' Dia Medium: 9.5'' H x 4.25'' Dia Large: 12'' H x 4.25'' Dia

ie. just take out those "x" before the sizes. Sorry about this...any help you can offer is much appreciated!

 

[JoeMo]

Thank you so much! It does give me the value I requested...now I'm seeing that I made an error...rather than

Small: 9'' H x 3'' Dia x Medium: 9.5'' H x 4.25'' Dia x Large: 12'' H x 4.25'' Dia

I need it to look like

Small: 9'' H x 3'' Dia Medium: 9.5'' H x 4.25'' Dia Large: 12'' H x 4.25'' Dia

ie. just take out those "x" before the sizes. Sorry about this...any help you can offer is much appreciated!

You are welcome.

You want to remove all the x's? Your example shows only the first x removed.

 

[eesterkin]

Hi...just the x's before the next size

Small: 9'' H x 3'' Dia Medium: 9.5'' H x 4.25'' Dia Large: 12'' H x 4.25'' Dia

So the x between dimensions stays...but the x before "Medium" is removed.

 

[eesterkin]

Nevermind...I used your Substitute formula to get it to work!

Thanks again for your help...spent hours trying to figure this out!!!