Split Cells in Range Based on Special Character

Alex O

Alex O

Active Member
Joined
Mar 16, 2009
Messages
345
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Sorry...
Caltrux_Member List_073020_AAOWRK 3.02.xlsb
ABCDEFGHIJK
1Member NameMEMBER NAMEADDRESSCITYSTATEZIPPHONE 1FAXEMAILWEB-ADDRESSCONTACT NAME
2•4 GEN LOGISTICS
319801 S. SANTA FE AVE.
4RANCHO DOMINGUEZ, CA 90221
5619-954-9194 • 909-401-2002 FAX WWW.4GENLOGISTICS.COM
6JENNIFER HUNTER
7JENNIFERH@4GENLOGISTICS.COM
8•A & A CONCRETE SUPPLY, INC.
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
Sheet7
 
Upvote 0
you can continue with this (there is no pattern except • so this is manual job)
CustomCNTCustom.1.1Custom.1.2.1Custom.1.2.2Custom.1.2.3Custom.1.3Custom.1.4Custom.1.5
•4 GEN LOGISTICS419801 S. SANTA FE AVE.RANCHO DOMINGUEZ CA 90221JENNIFER HUNTERJENNIFERH@4GENLOGISTICS.COM
•A & A CONCRETE SUPPLY, INC.43250 E. 70TH ST.LONG BEACH CA 90805-0209562-790-3650 562-790-3659 FAX WWW.AAREADYMIX.COMMIKE COOK
•MCRMC@SBCGLOBAL.NET24621 TELLER AVE. STE. 130NEWPORT BEACH CA 92660 949-253-2800
•WWW.AAREADYMIX.COM ALAN DAY58272 BERRY AVE.SACRAMENTO CA 95828916-383-3756WWW.AAREADYMIX.COM ALEX BAILEYABAILEY@AAREADYMIX.COM
•A & I EXPRESS, INC.2123 LEE RD., STE. EWATSONVILLE CA 95076-9422 831-763-7805 831-763-7890 FAX WWW.AIEXPRESSINC.COM
•IQBAL SINGH1IS@AITRUCKINGLLC.COM
•A & R TRANSPORT4194 N. RANCHO AVE.SAN BERNARDINO CA 92410 800-645-5645 EXT 3412 DRIVEWITHAR.COMCHRIS CONKLINCCONKLIN@ARTRANSPORT.COM
 
Upvote 0
ops, correction

CustomCNTCustom.1.1Custom.1.2.1Custom.1.2.2Custom.1.2.3Custom.1.3Custom.1.4Custom.1.5
•4 GEN LOGISTICS519801 S. SANTA FE AVE.RANCHO DOMINGUEZ CA 90221619-954-9194 • 909-401-2002 FAX WWW.4GENLOGISTICS.COMJENNIFER HUNTERJENNIFERH@4GENLOGISTICS.COM
•A & A CONCRETE SUPPLY, INC.43250 E. 70TH ST.LONG BEACH CA 90805-0209562-790-3650 562-790-3659 FAX WWW.AAREADYMIX.COMMIKE COOK
•MCRMC@SBCGLOBAL.NET24621 TELLER AVE. STE. 130NEWPORT BEACH CA 92660 949-253-2800
•WWW.AAREADYMIX.COM ALAN DAY58272 BERRY AVE.SACRAMENTO CA 95828916-383-3756WWW.AAREADYMIX.COM ALEX BAILEYABAILEY@AAREADYMIX.COM
•A & I EXPRESS, INC.2123 LEE RD., STE. EWATSONVILLE CA 95076-9422 831-763-7805 831-763-7890 FAX WWW.AIEXPRESSINC.COM
•IQBAL SINGH1IS@AITRUCKINGLLC.COM
•A & R TRANSPORT4194 N. RANCHO AVE.SAN BERNARDINO CA 92410 800-645-5645 EXT 3412 DRIVEWITHAR.COMCHRIS CONKLINCCONKLIN@ARTRANSPORT.COM
 
Upvote 0
sandy666 - I appreciate your willingness to assist, but I'm not sure I understand how you parsed out the text. Is there a formula in the CNT column?
 
Upvote 0
So i have a question are the Information in Sequence ( I Mean First Member Name..then Address.. City etc etc)
Also if there is less information will it be sufficient that the data be filled in the next available column (even though it may not relate to that head)
 
Upvote 0
1. your pattern is not proper (see post#5, column Custom.1.3, first row)
2. I forgot to remove CNT (it count rows for each Custom)
3. there is no any formula but Power Query M code
 
Upvote 0
CA_Punit said:
So i have a question are the Information in Sequence ( I Mean First Member Name..then Address.. City etc etc)
Also if there is less information will it be sufficient that the data be filled in the next available column (even though it may not relate to that head)
Click to expand...
CA_Punit -I appreciate your assistance. The answer to both questions is yes.
 
Upvote 0
sandy666 said:
1. your pattern is not proper (see post#5, column Custom.1.3, first row)
2. I forgot to remove CNT (it count rows for each Custom)
3. there is no any formula but Power Query M code
Click to expand...
Got it...thanks! I haven't tried PQ - perhaps I'll give it a try...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Macro to copy column from one sheet to another based on value in cell
Replies
0
Views
755
afarlow
A
R
  • Question
Userform issue/challenge - Copying listbox selection to a sheet
Replies
0
Views
725
Ronie
R
C
  • Question
Splitting a cell based on character count, with a header for each line
Replies
2
Views
430
ccwalker
C
T
  • Question
Calculate the Date of a Receipt Based on the Date of the Receipt in the Previous Month
Replies
0
Views
832
Tegglet
T
T
  • Question
Removing Special Characters From Certain Columns Loop
Replies
4
Views
2K
tripodgod
T

Forum statistics

Threads
1,214,784
Messages
6,121,536
Members
449,037
Latest member
tmmotairi

Share this page

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
Back
Top