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

 

[mikerickson]

Excel 2008 does not support VBA.
If your long string is in A1 of the active sheet, running this AppleScript will separate out the sub strings (overwriting the other cells of column A)

-- Apple Script

set myDelimiter to "\"" & "," & "\""

tell application "Microsoft Excel"
	set theString to "\"," & (value of range "A1") & ",\""
	
	set AppleScript's text item delimiters to {myDelimiter}
	
	set mySubStrings to text items of theString
	
	repeat with i from 1 to count of mySubStrings
		set value of (get offset range "A1" row offset i) to item i of mySubStrings
	end repeat
end tell

To run this open the Script Editor application and paste it into the window and press the Run button.

 

[Paolo1309]

Hey Rick
I was wondering if you still working on that code or perhaps you forgot. I appreciated all you did.
Thanks

 

[Rick Rothstein]

I was wondering if you still working on that code or perhaps you forgot. I appreciated all you did.

I had completely missed that your thread title said you were using Excel for a Mac 2008... the code I was trying to write for you would not have worked (I have a PC and write macoros in VBA), but mikerickson graciously jumped in with what I assume (from my past knowledge of his capabilities) is a working "Apple Script" for you to use (Message #21).