How to separate text + number from one cell into rows and analyse duplicates in Excel for Mac 2008

[Paolo1309]

Dear All

I am using Excel for Mac 2008 and I can't activate the VBE function. I have a very long code made of numbers and text as the example below:

"661270027-2","1465607302-2","600752322-2","2704608-2","560073572-2","1249297624-2","556533864-2","561251776-2","572372400-2","548584429-3","548584429-0","512879918-2","511218371-2","841230300-2","596398500-2","666150726-2"

The codes are all separated by -#"," and when I extract them and paste them on Excel they all go into the same cell A1 example below:

[TABLE="width: 386"]
<tbody>[TR]
[TD="width: 386, colspan: 6"]A1
661270027-2,"1465607302- 2","6007523222","27046082","560073572-2","[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

1) I would like to separate the long code into different rows and get rid off the -#"," at the same time so i can keep just the numeric codes I am interested about. example below:


[TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl24, width: 65, align: right"]B1
661270027[/TD]
[/TR]
[TR]
[TD="align: right"]C1 1465607302[/TD]
[/TR]
[TR]
[TD="align: right"]D1
600752322[/TD]
[/TR]
[TR]
[TD="align: right"]E1
2704608[/TD]
[/TR]
[TR]
[TD="align: right"]F1
560073572
[/TD]
[/TR]
</tbody>[/TABLE]

2) I would like Excel to find any duplicates among the clean codes (if any) and alert me somehow (highlight is ok)

3) I would like Excel to "tell me" which codes duplicate the most (if any) perhaps organising them by numerical order (starting with the one who duplicates more often..)

I know I am asking a lot but all your help is very very much appreciated.

Cheers

 

[Rick Rothstein]

Unfortunately it does not work. Tells me formula is too long.

I was kind of afraid of that given the size of the text in the cell. So are you ready for a VBA solution (macro) yet?

 

[Paolo1309]

Sure! and thank you in advance!!!

 

[István Hirsch]

Give this formula a try (enter it into B1, then drag it down and right):

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,""",""","-"),"""",""),"-",REPT(" ",999)),1998*COLUMN(A1)-1997,999))

 

[Rick Rothstein]

Sure! and thank you in advance!!!

Whoops... I just re-read your original message (and the thread title too) where you said you were using Excel for Mac 2008 which does not support VBA macros, so I cannot write one for that will work on your computer. Sorry, but I am out of ideas. Is there anyway you can get the source of your text to split the values down to a more mangeable size at that source's end?

 

[Paolo1309]

I guess I can get hold of a PC with office though. If CBA macros would solve my problems.

 

[Rick Rothstein]

I guess I can get hold of a PC with office though. If CBA macros would solve my problems.

Okay, I'll write one up for you. One question though... do you only have cell A1 filled with your text of do you multiple cells in Column A with text in each cell that need to be split out?

 

[Paolo1309]

Yes all 18000+ characters are in cell A1. but splitting is only the first thing I need to do I had 2 more points:

2) I would like Excel to find any duplicates among the clean codes (if any) and alert me somehow (highlight is ok)

3) I would like Excel to "tell me" which codes duplicate the most (if any) perhaps organising them by numerical order (starting with the one who duplicates more often..)

THANK YOU SO MUCH RICK!!!

 

[Rick Rothstein]

Yes all 18000+ characters are in cell A1. but splitting is only the first thing I need to do I had 2 more points:

2) I would like Excel to find any duplicates among the clean codes (if any) and alert me somehow (highlight is ok)

3) I would like Excel to "tell me" which codes duplicate the most (if any) perhaps organising them by numerical order (starting with the one who duplicates more often..)

THANK YOU SO MUCH RICK!!!

Okay, initially then, only one cell has to be processed. You asked for the split out cells to go across Row 1... is there a particular need for that orientation or could I split them down the column instead (there are many more rows than columns and data is easier to scroll and read when it is located down a column)?

 

[Rick Rothstein]

Okay, initially then, only one cell has to be processed. You asked for the split out cells to go across Row 1... is there a particular need for that orientation or could I split them down the column instead (there are many more rows than columns and data is easier to scroll and read when it is located down a column)?

Actually, I am going to take that decision away from you and list the split out codes down Column A... that way, I can put the number of times code is repeated next to it in Column B and the an index number of 1, 2, 3, etc. in Column C. That way, the cells in Column B will have number only if its corresponding value in Column A is part of a duplicate. And, you can then select Columns A, B and C and sort on Column B to find out which ones are repeated the most... sorting on Column C would allow you return the list back to it original order.

 

[Paolo1309]

Of course. whatever is easier and works