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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0
No you just select your columns from Excel copy them all and then paste it on this space

i hope its clear



Regards
 
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,620
Messages
6,143,520
Members
450,492
Latest member
Rusbus1972

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
Back
Top