Split Cells in Range Based on Special Character

[Alex O]

I've been trying various methods for three days now...I'm hoping one of you experts can offer a solution. The data set below is 6976 rows of member information. In order to upload the file it has to be in columns as illustrated below. The only identifiable pattern is the CHAR(149) that leads each member name. Is there a formula or vba that I can use to check each row, and if it begins with CHAR(149) parse the corresponding rows so the data is pasted into the appropriate column, then go to the next instance of LEFT(A:A,1)=CHAR(149) and repeat? One of the main challenges is that not all members have provided the same information (see •IQBAL SINGH as example). Any suggestions would be appreciated. Alex

 

[sandy666]

sure

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF = Table.AddColumn(Source, "Custom", each if Text.StartsWith([Member Name], "•") then [Member Name] else null),
    Fill = Table.FillDown(IF,{"Custom"}),
    Filter = Table.SelectRows(Fill, each not Text.StartsWith([Member Name], "•")),
    Group = Table.Group(Filter, {"Custom"}, {{"Count", each _, type table}, {"CNT", each Table.RowCount(_), type number}}),
    List = Table.AddColumn(Group, "Custom.1", each [Count][Member Name]),
    Extract = Table.TransformColumns(List, {"Custom.1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    SplitPipe = Table.SplitColumn(Extract, "Custom.1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4", "Custom.1.5"}),
    SplitComma = Table.SplitColumn(SplitPipe, "Custom.1.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1.2.1", "Custom.1.2.2", "Custom.1.2.3"})
in
    SplitComma

you can continue to get proper result

 

[sandy666]

I have to go but I'll be back in an hour or so

 

[CA_Punit]

Using Formula you can try this

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Member Name	MEMBER NAME	ADDRESS	CITY	STATE	ZIP	PHONE 1	FAX	EMAIL	WEB-ADDRESS	CONTACT NAME
2	•4 GEN LOGISTICS	•4 GEN LOGISTICS	19801 S. SANTA FE AVE.	RANCHO DOMINGUEZ, CA 90221	619-954-9194 • 909-401-2002 FAX WWW.4GENLOGISTICS.COM	JENNIFER HUNTER	JENNIFERH@4GENLOGISTICS.COM
3	19801 S. SANTA FE AVE.	•A & A CONCRETE SUPPLY, INC.	3250 E. 70TH ST.	LONG BEACH, CA 90805-0209	562-790-3650 562-790-3659 FAX WWW.AAREADYMIX.COM	MIKE COOK
4	RANCHO DOMINGUEZ, CA 90221	•MCRMC@SBCGLOBAL.NET	4621 TELLER AVE. STE. 130	NEWPORT BEACH, CA 92660, 949-253-2800
5	619-954-9194 • 909-401-2002 FAX WWW.4GENLOGISTICS.COM	•WWW.AAREADYMIX.COM ALAN DAY	8272 BERRY AVE.	SACRAMENTO, CA 95828	916-383-3756	WWW.AAREADYMIX.COM ALEX BAILEY	ABAILEY@AAREADYMIX.COM
6	JENNIFER HUNTER	•A & I EXPRESS, INC.	123 LEE RD., STE. E	WATSONVILLE, CA 95076-9422, 831-763-7805 831-763-7890 FAX WWW.AIEXPRESSINC.COM
7	JENNIFERH@4GENLOGISTICS.COM	•IQBAL SINGH	IS@AITRUCKINGLLC.COM
8	•A & A CONCRETE SUPPLY, INC.	•A & R TRANSPORT	194 N. RANCHO AVE.	SAN BERNARDINO, CA 92410, 800-645-5645 EXT 3412 DRIVEWITHAR.COM	CHRIS CONKLIN	CCONKLIN@ARTRANSPORT.COM
9	3250 E. 70TH ST.
10	LONG BEACH, CA 90805-0209
11	562-790-3650 562-790-3659 FAX WWW.AAREADYMIX.COM
12	MIKE COOK
13	•MCRMC@SBCGLOBAL.NET
14	4621 TELLER AVE. STE. 130
15	NEWPORT BEACH, CA 92660, 949-253-2800
16	•WWW.AAREADYMIX.COM ALAN DAY
17	8272 BERRY AVE.
18	SACRAMENTO, CA 95828
19	916-383-3756
20	WWW.AAREADYMIX.COM ALEX BAILEY
21	ABAILEY@AAREADYMIX.COM
22	•A & I EXPRESS, INC.
23	123 LEE RD., STE. E
24	WATSONVILLE, CA 95076-9422, 831-763-7805 831-763-7890 FAX WWW.AIEXPRESSINC.COM
25	•IQBAL SINGH
26	IS@AITRUCKINGLLC.COM
27	•A & R TRANSPORT
28	194 N. RANCHO AVE.
29	SAN BERNARDINO, CA 92410, 800-645-5645 EXT 3412 DRIVEWITHAR.COM
30	CHRIS CONKLIN
31	CCONKLIN@ARTRANSPORT.COM										1
Sheet8

Cell Formulas
Range	Formula
C2:H2,C8:G8,C7:D7,C6:E6,C5:H5,C4:E4,C3:G3	C2	=TRANSPOSE(INDEX(IF(OFFSET(INDEX($A$2:$A$31,AGGREGATE(15,6,SEARCH(B2,$A$2:$A$31)*ROW($A$1:$A$30),1)),1,,10)=B3,"",OFFSET(INDEX($A$2:$A$31,AGGREGATE(15,6,SEARCH(B2,$A$2:$A$31)*ROW($A$1:$A$30),1)),1,,10)),ROW(INDIRECT("A1:A"&AGGREGATE(15,6,SEARCH("PUNIT",IF(OFFSET(INDEX($A$2:$A$31,AGGREGATE(15,6,SEARCH(B2,$A$2:$A$31)*ROW($A$1:$A$30),1)),1,,10)=B3,"PUNIT",OFFSET(INDEX($A$2:$A$31,AGGREGATE(15,6,SEARCH(B2,$A$2:$A$31)*ROW($A$1:$A$30),1)),1,,10)))*ROW($A$1:$A$30),1))),1))
B2:B31	B2	=IFERROR(INDEX($A$2:$A$31,AGGREGATE(15,6,1/(1/((CODE($A$2:$A$31)=149)*ROW($A$1:$A$30))),ROWS($A$1:A1))),"")
Dynamic array formulas.

 

[Alex O]

CA_Punit - I appreciate the assistance. I think with some tweaking your method could potentially work, however, with 6932 rows it takes quite some time to update the file. I'm running MSO365 Pro+ on a 10th Gen i7 and it's taking quite a while to complete. It's still better than anything I was able to come up with, so I definitely appreciate it. I'll continue experimenting with it and let you know what (if anything) I come up with. Thanks Again, Alex

 

[Alex O]

I have to go but I'll be back in an hour or so

Thanks, Sandy!

 

[Fluff]

I'm running MSO365 Pro+

Can you please update your account details to show this, as it lets us know what functions you have available.

 

[Alex O]

Can you please update your account details to show this, as it lets us know what functions you have available.

Done! I didn't realize it was so outdated....

 

[CA_Punit]

Formula will slow down as i am using Indirect & offset both volatile function.. Give it a try

 

[Fluff]

Done! I didn't realize it was so outdated....

Thanks