looking to simplify numbers. remove slashes and show sequences. any help would be apreciated

[bmuddy2]

Book1
	A	B	C	D	E	F	G	H
2		A is the original number as written.
3
4		84535831 / 2 (2) this can be broken down to read 84535831 and 84535832. (2) means 2 people
5
6		column B, removed the / and replaced the last number with what was after the /.
7		C,D,E if more than one / exists. No more than 4 should be there.
8
9		Column f is whats in the () or place a 1 if () does not exist.
10
11		A	B	C	D	E	F	G
12		A, original number.	B, no /'s or ()'s.	C, 84535831 becomes 84535832.	D, same as C but the 2nd /.	E, same as D but the 3rd /.	F, same as E but the 4th /.	G
13	1	84535831 / 2 (2)	84535831	84535832				2
14	2	84506770 / 1	84506770	84506771				1
15	3	84074165/ 6 / 7	84074165	84074166	84074167			1
16	4	23408830	23408830					1
17	5	84330244	84330244					1
18	6	84524615	84524615					1
19	7	84280873	84280873					1
20	8	84145691	84145691					1
21	9	84212543	84212543					1
22	10	84378246 (2)	84378246					2
23	11	84448335 / 6 / 7 / 8	84448335	84448336	84448337	84448338		1
24	12	84195536 / 7	84195536	84195537				1
25	13	23324603 / 4	23324603	23324604				1
26	14	84280872	84280872					1
27	15	84196824	84196824					1
28	16	6002TM0519/20	6002TM0519	6002TM0520				1
29	17	6002TM0521/22	6002TM0521	6002TM0522				1
30	18	84428905	84428905					1
31	19	84428906	84428906					1
32	20	84428907	84428907					1
33	21	84017727	84017727					1
34	22	84400203 (3)	84400203					3
35	23	22939891 (2)	22939891					2
36	24	84319671 (2)	84319671					2
37	25	84013501 (2)	84013501					2
38	26	84052648 (2)	84052648					2
39	27	26213538 (2)	26213538					2
40	28	42333372 / 3 (2)	42333372	42333373				2
41	29	80307495 (2)	80307495					2
42	30	23241470	23241470					1
43	31	84492946	84492946					1
44	32	84377233	84377233					1
45	33	84206594	84206594					1
46	34	23326349	23326349					1
47	35	84206594 (1)	84206594					1
Sheet2

 

[Rick Rothstein]

Assuming your data starts on Row 1 in Column A, put this formula in cell B1...

=TRIM(LEFT(A1,FIND("/",REPLACE(A1,FIND("(",A1&"("),99,"")&"/")-1))

then put this formula in cell C1 and copy it across to cell E1...

=IF(LEN(TRIM(MID(SUBSTITUTE(LEFT($A1,FIND("(",$A1&"(")-1),"/",REPT(" ",99)),COLUMNS($F:F)*99,99))),TRIM(LEFT($A1,FIND("/",REPLACE($A1,FIND("(",$A1&"("),99,"")&"/")-1))&TRIM(MID(SUBSTITUTE(LEFT($A1,FIND("(",$A1&"(")-1),"/",REPT(" ",99)),COLUMNS($F:F)*99,99)),"")

and finally put this formula in cell F1...

=IFERROR(-MID(A1,FIND("(",A1),9),1)

Once you have done that, select the range B1:F1 and copy it down to the bottom of your data.

 

[lrobbo314]

If you have PowerQuery available on your version of Excel at work, you can also accomplish this. I was able to do it, but get the output to be a more proper data set in the end compared to what you requested. Here's how it came out.

[TABLE="width: 147"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Number[/TD]
[TD]Parens[/TD]
[/TR]
[TR]
[TD]84535831[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]84535832[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]84506770[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84506771[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84074165[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84074166[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84074167[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84448335[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84448336[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84448337[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84448338[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84195536[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]84195537[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]23324603[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]23324604[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6002TM0519[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6002TM0520[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6002TM0521[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6002TM0522[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]42333372[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]42333373[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

But, Rick's formula solution is great and will probably do exactly what you need. Just throwing it out there as an alternative to formulas like that which make my head hurt.

 

[bmuddy2]

thanks rick. seems to be working.

IROBBO314, Ill have to check on that. I know its excel 365 and honestly I've only been using that version for a few weeks.