Split Cells in Range Based on Special Character

Alex O

Active Member
Joined
Mar 16, 2009
Messages
343
Office Version
  1. 365
Platform
  1. Windows
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

1597071464774.png
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
sure
Rich (BB code):
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
Using Formula you can try this

Book1
ABCDEFGHIJK
1Member NameMEMBER NAMEADDRESSCITYSTATEZIPPHONE 1FAXEMAILWEB-ADDRESSCONTACT NAME
2•4 GEN LOGISTICS•4 GEN LOGISTICS19801 S. SANTA FE AVE.RANCHO DOMINGUEZ, CA 90221619-954-9194 • 909-401-2002 FAX WWW.4GENLOGISTICS.COMJENNIFER HUNTERJENNIFERH@4GENLOGISTICS.COM
319801 S. SANTA FE AVE.•A & A CONCRETE SUPPLY, INC.3250 E. 70TH ST.LONG BEACH, CA 90805-0209562-790-3650 562-790-3659 FAX WWW.AAREADYMIX.COMMIKE COOK
4RANCHO DOMINGUEZ, CA 90221•MCRMC@SBCGLOBAL.NET4621 TELLER AVE. STE. 130NEWPORT BEACH, CA 92660, 949-253-2800
5619-954-9194 • 909-401-2002 FAX WWW.4GENLOGISTICS.COM•WWW.AAREADYMIX.COM ALAN DAY8272 BERRY AVE.SACRAMENTO, CA 95828916-383-3756WWW.AAREADYMIX.COM ALEX BAILEYABAILEY@AAREADYMIX.COM
6JENNIFER HUNTER•A & I EXPRESS, INC.123 LEE RD., STE. EWATSONVILLE, CA 95076-9422, 831-763-7805 831-763-7890 FAX WWW.AIEXPRESSINC.COM
7JENNIFERH@4GENLOGISTICS.COM•IQBAL SINGHIS@AITRUCKINGLLC.COM
8•A & A CONCRETE SUPPLY, INC.•A & R TRANSPORT194 N. RANCHO AVE.SAN BERNARDINO, CA 92410, 800-645-5645 EXT 3412 DRIVEWITHAR.COMCHRIS CONKLINCCONKLIN@ARTRANSPORT.COM
93250 E. 70TH ST. 
10LONG BEACH, CA 90805-0209 
11562-790-3650 562-790-3659 FAX WWW.AAREADYMIX.COM 
12MIKE COOK 
13•MCRMC@SBCGLOBAL.NET 
144621 TELLER AVE. STE. 130 
15NEWPORT BEACH, CA 92660, 949-253-2800 
16•WWW.AAREADYMIX.COM ALAN DAY 
178272 BERRY AVE. 
18SACRAMENTO, CA 95828 
19916-383-3756 
20WWW.AAREADYMIX.COM ALEX BAILEY 
21ABAILEY@AAREADYMIX.COM 
22•A & I EXPRESS, INC. 
23123 LEE RD., STE. E 
24WATSONVILLE, CA 95076-9422, 831-763-7805 831-763-7890 FAX WWW.AIEXPRESSINC.COM 
25•IQBAL SINGH 
26IS@AITRUCKINGLLC.COM 
27•A & R TRANSPORT 
28194 N. RANCHO AVE. 
29SAN BERNARDINO, CA 92410, 800-645-5645 EXT 3412 DRIVEWITHAR.COM 
30CHRIS CONKLIN 
31CCONKLIN@ARTRANSPORT.COM 1
Sheet8
Cell Formulas
RangeFormula
C2:H2,C8:G8,C7:D7,C6:E6,C5:H5,C4:E4,C3:G3C2=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:B31B2=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

Active Member
Joined
Mar 16, 2009
Messages
343
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Mar 16, 2009
Messages
343
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
Formula will slow down as i am using Indirect & offset both volatile function.. Give it a try
 

Watch MrExcel Video

Forum statistics

Threads
1,122,850
Messages
5,598,457
Members
414,239
Latest member
xnanx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top