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:

A1 661270027-2,"1465607302- 2","6007523222","27046082","560073572-2","
B1
C1

<tbody>
</tbody>

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:

B1 661270027

C1 1465607302

D1 600752322

E1 2704608

F1 560073572

<tbody>
</tbody>

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]

Is the data in one cell a long string of numerals, with quote marks, dashes and commas

If "12345-6" is in A1,
=LEFT(A1, FIND("-", A1&"-")-1) will return 12345 and (if that formula is in B1)
=SUBTITUTE(SUBSTITUTE(A1, B1, "", 1), "-","") will return 6

You could also look at TextToColumns to separate things out.

 

[Rick Rothstein]

Seems like there should be a shorter formula for this, but this was all I could come up with. With your text in cell A1, put this formula in cell B1 and copy it across for as many columns as you think you will ever fill with code numbers, then copy all those cells down to the bottom of your data...

=LEFT(TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"""",""),",",REPT(" ",500)),COLUMN(A1)*500-499,500)),FIND("-",TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"""",""),",",REPT(" ",500)),COLUMN(A1)*500-499,500))&"-")-1)

 

[Paolo1309]

I am not sure I understood correctly. But my all text is in A1. I put the formula in B1 and copy across for as many columns and rows. Nothing happens..

 

[Rick Rothstein]

I am not sure I understood correctly. But my all text is in A1. I put the formula in B1 and copy across for as many columns and rows. Nothing happens..

It works for me in my samples. There can be issues with the formula if the text is too big or if there are too many delimited items... how big is the text in cell A1? Put =LEN(A1) in a blank cell and tell me the number. About how many commas are in your largest cell?

 

[dispelthemyth]

CAn confirm Rick's code works, you should see the below using it which appears to be what you want

Excel Workbook
	A	B	C	D	E	F	G
1	661270027-2,"1465607302- 2","6007523222","27046082","560073572-2","	661270027	1465607302	6007523222	27046082	560073572
2
3
4
ECOSHEET 2

I am not sure I understood correctly. But my all text is in A1. I put the formula in B1 and copy across for as many columns and rows. Nothing happens..

 

[Paolo1309]

18587 characters

 

[Paolo1309]

Thanks!! but my A1 cell has 18587 characters. Could that be the problem??

 

[Rick Rothstein]

Thanks!! but my A1 cell has 18587 characters. Could that be the problem??

Sure is! I do not think my method will work for you as that many characters coupled with the number of commas that implies guarantees that my approach cannot work. Do you actually need a formula solution or would a manual method be okay? If so, I would like to resurrect Mike's Text To Columns suggestion back from Message #2, but with a modification. First, select Column A and copy/paste it to Column B (because I am assuming you want to keep your original data intact). Then select Column B and call up Excel's Replace dialog box (press CTRL+H), put a single quote mark (") in the "Find what" field, and leave the "Replace with" field empty, then click the "Options>>" button and make sure the "Match entire cell contents" check box is NOT checked, then click the "Replace All" button. Now, with Column B still selected, call up the "Text To Columns" dialog box (pressing ALT+de should be a shortcut for it). This is a 3 step dialog. On Step #1, select the "Delimited" option box and click "Next". On Step #2, uncheck any checked check boxes and then put a check mark in the one labeled "Comma" and click the "Finish" button (there is nothing we need to do at Step #3 for what you want). If everything went okay (I do not know if Text To Columns has any maximum size limits or not), your text should be split apart as you wanted. If this did not work, or if you do not want a manual solution, then I am pretty sure you only other option is to use a VBA macro.

 

[Paolo1309]

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