Extracting from a column

[MiguelS]

IV:9710388235LADD DISTRIBUTION LLC

Did a search on extract, but couldnt find a solution, so I wanted to see if someone could help out.

I want to populate 3 columns from 1 column.

Column 1 would have IV: - simple enough as I can trim 3 characters.

Column 2 would have 9710388235 - Anything after char # 3, but up to the last Numerical DIgit.

Column 3, LADD DISTRIBUTION LLC - Everything after the Numerical value.

 

[MiguelS]

My data
Excel 2016 (Windows) 32 bit

	A	B	C	D
1	2110-00-00	9	7/3/2017	Py:55668003 ALLIED ELECTRONICS INC.
2	2110-00-00	9	7/3/2017	IV:JUL 2017DOUG LANAGAN
3	2110-00-00	9	7/3/2017	IV:JUL 17WILLIAM THAYER
4	2110-00-00	9	7/3/2017	IV:JUL 22 - AUG 21JOHN HANCOCK LIFE INSURANCE
5	2110-00-00	9	7/5/2017	Py:55668004 HEILIND ELECTRONICS
6	2110-00-00	9	7/5/2017	Py:129244 AEROTEK, INC
7	2110-00-00	9	7/5/2017	Py:129245 ANIXTER INC.
8	2110-00-00	9	7/5/2017	Py:129246 APPLEONE EMPLOYMENT SERVICES
9	2110-00-00	9	7/5/2017	Py:129247 ARROW ELECTRONICS INC.
10	2110-00-00	9	7/5/2017	Py:129248 AUDI FINANCIAL SERVICES
11	2110-00-00	9	7/5/2017	Py:129249 BUCKLES-SMITH
12	2110-00-00	9	7/5/2017	Py:129250 CHUCK McMAHON
13	2110-00-00	9	7/5/2017	Py:129251 CIRRIS SYSTEMS CORP.
14	2110-00-00	9	7/5/2017	Py:129252 CITY OF DEMING
15	2110-00-00	9	7/5/2017	Py:129253 CREATIVE MANUFACTURING SOLUTIONS
16	2110-00-00	9	7/5/2017	Py:129254 DF SALES CO.
17	2110-00-00	9	7/5/2017	Py:129255 DIGI-KEY 97789
18	2110-00-00	9	7/5/2017	Py:129256 EMPIRICAL SYSTEMS, INC.
19	2110-00-00	9	7/5/2017	Py:129257 EPICOR SOFTWARE CORPORATION
20	2110-00-00	9	7/5/2017	Py:129258 EXPRESS SERVICES, INC

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[Rick Rothstein]

Oh wow, I was not expecting your data in Column D to look like that. For the first breakout column for Rows 2, 3 and 4 in your example data... can we assume you want the date and date ranges to go there (same column as the numbers)?

 

[MiguelS]

yes. The other issue I have when I tested your formulas was with row 17

 

[Rick Rothstein]

yes. The other issue I have when I tested your formulas was with row 17

Is it always the case that when a non-number follows the colon, that it is always a 3-character month name abbreviation?

 

[MiguelS]

No, but I am sure it is 85% of the time.

 

[Rick Rothstein]

No, but I am sure it is 85% of the time.

85% of the time is not consistent enough to use for a formula. Can you show some representative examples of records where Column D has non-digits immediately after the colon that do not form a month abbreviation?

 

[MiguelS]

Excel 2016 (Windows) 32 bit
	A	B	C	D
79	2110-00-00	9	7/5/2017	IV:894-975327ANIXTER INC.
80	2110-00-00	9	7/5/2017	IV:894-975328ANIXTER INC.
81	2110-00-00	9	7/5/2017	IV:1300666392HARTING, INC. OF NORTH AMERICA
82	2110-00-00	9	7/5/2017	IV:ME1614-02MASTER ELECTRONICS
83	2110-00-00	9	7/5/2017	IV:ME1702-02MASTER ELECTRONICS
84	2110-00-00	9	7/5/2017	IV:MF3373-02MASTER ELECTRONICS
85	2110-00-00	9	7/5/2017	IV:37761715MCMASTER-CARR SUPPLY CO
86	2110-00-00	9	7/5/2017	IV:C383374OMEGA ENGINEERING INC.
87	2110-00-00	9	7/5/2017	IV:C383404OMEGA ENGINEERING INC.
88	2110-00-00	9	7/5/2017	IV:C383436OMEGA ENGINEERING INC.
89	2110-00-00	9	7/5/2017	IV:N269504OMEGA ENGINEERING INC.
90	2110-00-00	9	7/5/2017	IV:N269524OMEGA ENGINEERING INC.
91	2110-00-00	9	7/5/2017	IV:N269547OMEGA ENGINEERING INC.
92	2110-00-00	9	7/5/2017	IV:195439POWERMATIC ASSOCIATES
93	2110-00-00	9	7/5/2017	IV:3294902POWERMATIC ASSOCIATES
94	2110-00-00	9	7/5/2017	IV:3294903POWERMATIC ASSOCIATES
95	2110-00-00	9	7/5/2017	IV:3294904POWERMATIC ASSOCIATES
96	2110-00-00	9	7/5/2017	IV:3294905POWERMATIC ASSOCIATES
97	2110-00-00	9	7/5/2017	IV:7759035WES-GARDE COMPONENTS GROUP
Sheet1

 

[AlKey]

So it is the Col D that needs to separated?
Enter formula in E1 and copy across and down

[B]=TRIM(MID(SUBSTITUTE(":"&REPLACE($D1,LOOKUP(1,-MID($D1,ROW($D$1:$D$200),1),ROW($D$1:$D$200))+1,,":"),":",REPT(" ",200)),200*COLUMNS($D:D),200))[/B]

Book1
	D	E	F	G
1	IV:9710388235LADD DISTRIBUTION LLC	IV	9710388235	LADD DISTRIBUTION LLC
2	IV:1300666392HARTING, INC. OF NORTH AMERICA	IV	1300666392	HARTING, INC. OF NORTH AMERICA
3	IV:ME1614-02MASTER ELECTRONICS	IV	ME1614-02	MASTER ELECTRONICS
4	IV:ME1702-02MASTER ELECTRONICS	IV	ME1702-02	MASTER ELECTRONICS
5	IV:MF3373-02MASTER ELECTRONICS	IV	MF3373-02	MASTER ELECTRONICS
6	IV:37761715MCMASTER-CARR SUPPLY CO	IV	37761715	MCMASTER-CARR SUPPLY CO
7	IV:7759035WES-GARDE COMPONENTS GROUP	IV	7759035	WES-GARDE COMPONENTS GROUP
Sheet2

 

[MiguelS]

Thank you ALL for your help

 

[Rick Rothstein]

So it is the Col D that needs to separated?
Enter formula in E1 and copy across and down

[B]=TRIM(MID(SUBSTITUTE(":"&REPLACE($D1,LOOKUP(1,-MID($D1,ROW($D$1:$D$200),1),ROW($D$1:$D$200))+1,,":"),":",REPT(" ",200)),200*COLUMNS($D:D),200))[/B]

That is a nice formula, but it is not perfect as it fails for this Column D value that the OP showed in Message #11...

Py:129255 DIGI-KEY 97789

Just so the OP knows, this formula will fail whenever there are digits embedded in or following the company name. Still, this should cover more than 99% of the OP's needs.