Split a Cell : Need Help

deven_deesha

Board Regular
Joined
Jan 5, 2006
Messages
134
I have a row as follow .

COL B has a data seperated by Alt + Enter. There is a duplciate data.

If there is a duplicate data then i want to neglect it and consider only one.

ex. Consider the following example

SUIT/ADR is may times in COL B. This is a input File.

COL A COL B
1 Suit/ADR

Suit/ADR

Suit/ADR

Suit/ADR

Claim Folder

2 Claim Folder

Claim Mgmt


I want the output in new file as

1 SUIT/ADR

1 Claim Folder

2 Claim Folder

2 Claim Mgmt


I tried to write a macro using Split function. But I stucked. Can you please help me?


Thanks

Shriswaroop
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

pooj_malh

New Member
Joined
Sep 25, 2006
Messages
26
Your question is a little confusing.

Can you put data in excel, copy it from there and paste here for both input as well as output

Regards
Pooja
 

pooj_malh

New Member
Joined
Sep 25, 2006
Messages
26
No you just select your columns from Excel copy them all and then paste it on this space

i hope its clear



Regards
 

deven_deesha

Board Regular
Joined
Jan 5, 2006
Messages
134

ADVERTISEMENT

Consider there are two columns in excel sheet.

In Second column there is a data seperated by ALT + Enter. here second Column is COL B. In COL B Duplciate Data is available so I want to remove it. you can copy this data in excel sheet. But While Copying in COLB you just press ALT + ENTER after each String "SUIT/ADR" so it will be your input file. Like this I have a huge Data.


ROW ID COL A COL B
1 1 SUIT/ADR
SUIT/ADR
SUIT/ADR
SUIT/ADR

I hope it is clear. If not please let me know I willtry to explain in more detail.

Thanks for your help

Shriswaroop

[/url]
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

Try Data > Filter > Advanced Filter.There is an option for unique records.

HTH
 

deven_deesha

Board Regular
Joined
Jan 5, 2006
Messages
134

ADVERTISEMENT

No. I have the duplicate data in the same cell.

Ex

Cell A2 has "SUIT/ADR" word 10 times. and after each word there is a alt + enter. I want it only once.

Cell A2

SUIT/ADR

SUIT/ADR

SUIT/ADR

SUIT/ADR


I hope it is clear
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Go to Data > Text to columns..

check on delimited> next > check others and type Alt+010(hold down Alt key and type 010) > Next > Finish.

HTH
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
try
Code:
Sub test()
Dim a, x, i As Long, b(), n As Long, e, myNum, c, myTxt, ii As Integer
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
a = Range("a1",Range("a" & Rows.Count).End(xlUp)).Value
ReDim b(1 To Rows.Count, 1 To 1)
With CreateObject("VBScript.RegExp")
   .Pattern = "^\d+"
   For Each e In a
      If InStr(e,Chr(10))>0 Then
         If .test(e) then
            myNum = .execute(e)(0)
         Else
            myNum = Empty
         End If
         myTxt = .replace(e,"")
         n = n + 1 : b(n,1) = myNum & myTxt
         x = Split(e,Chr(10))
         For ii = 1 To UBound(x)
            If Not dic.exists(x(ii)) Then
               n = n + 1
               b(n,1) = myNum & x(ii)
               dic.add x(ii), Nothing
            End If
         Next
      Else
         n = n + 1 : b(n,1) = e
      End If
      dic.removeall : myTxt = Empty : myNum = Empty
   Next
End With
Set dic = Nothing : Erase a
Range("c1").Resize(n).Value = b
End Sub
 

deven_deesha

Board Regular
Joined
Jan 5, 2006
Messages
134
you are bit near on the track But not exactly. I will explain more in detail.

In your code you have used

.Pattern = "^\d+"

Can you explain what it is?

For Each e In a

This sentence populates the following value in e

"Suit/ADR Suit/ADR Suit/ADR Suit/ADR" Spaces are there in between each word

and

x = Split(e, Chr(10))

above statement has value

x(0) = "Suit/ADR"
x(1) = " "
x(2) = "Suit/ADR " one space is there after each word
x(3) = " "
x(4) = "Suit/ADR "
x(5) = " "
x(6) = "Suit/ADR "

etc

I tried to use Chr(13) instead of Chr(10) but doesn't work :(

Can you please provide me some more help.

Thanks,

Shriswaroop
 

Forum statistics

Threads
1,136,369
Messages
5,675,361
Members
419,565
Latest member
Phil57

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
Top