Extracting from a column

MiguelS

New Member
Joined
Apr 8, 2016
Messages
18
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.
 
My data
Excel 2016 (Windows) 32 bit
ABCD
2110-00-00Py:55668003 ALLIED ELECTRONICS INC.
2110-00-00IV:JUL 2017DOUG LANAGAN
2110-00-00IV:JUL 17WILLIAM THAYER
2110-00-00IV:JUL 22 - AUG 21JOHN HANCOCK LIFE INSURANCE
2110-00-00Py:55668004 HEILIND ELECTRONICS
2110-00-00Py:129244 AEROTEK, INC
2110-00-00Py:129245 ANIXTER INC.
2110-00-00Py:129246 APPLEONE EMPLOYMENT SERVICES
2110-00-00Py:129247 ARROW ELECTRONICS INC.
2110-00-00Py:129248 AUDI FINANCIAL SERVICES
2110-00-00Py:129249 BUCKLES-SMITH
2110-00-00Py:129250 CHUCK McMAHON
2110-00-00Py:129251 CIRRIS SYSTEMS CORP.
2110-00-00Py:129252 CITY OF DEMING
2110-00-00Py:129253 CREATIVE MANUFACTURING SOLUTIONS
2110-00-00Py:129254 DF SALES CO.
2110-00-00Py:129255 DIGI-KEY 97789
2110-00-00Py:129256 EMPIRICAL SYSTEMS, INC.
2110-00-00Py:129257 EPICOR SOFTWARE CORPORATION
2110-00-00Py:129258 EXPRESS SERVICES, INC

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/3/2017[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/3/2017[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/3/2017[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/3/2017[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]7/5/2017[/TD]

</tbody>
Sheet1
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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)?
 
Upvote 0
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?
 
Last edited:
Upvote 0
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?
 
Upvote 0

Excel 2016 (Windows) 32 bit
ABCD
792110-00-0097/5/2017IV:894-975327ANIXTER INC.
802110-00-0097/5/2017IV:894-975328ANIXTER INC.
812110-00-0097/5/2017IV:1300666392HARTING, INC. OF NORTH AMERICA
822110-00-0097/5/2017IV:ME1614-02MASTER ELECTRONICS
832110-00-0097/5/2017IV:ME1702-02MASTER ELECTRONICS
842110-00-0097/5/2017IV:MF3373-02MASTER ELECTRONICS
852110-00-0097/5/2017IV:37761715MCMASTER-CARR SUPPLY CO
862110-00-0097/5/2017IV:C383374OMEGA ENGINEERING INC.
872110-00-0097/5/2017IV:C383404OMEGA ENGINEERING INC.
882110-00-0097/5/2017IV:C383436OMEGA ENGINEERING INC.
892110-00-0097/5/2017IV:N269504OMEGA ENGINEERING INC.
902110-00-0097/5/2017IV:N269524OMEGA ENGINEERING INC.
912110-00-0097/5/2017IV:N269547OMEGA ENGINEERING INC.
922110-00-0097/5/2017IV:195439POWERMATIC ASSOCIATES
932110-00-0097/5/2017IV:3294902POWERMATIC ASSOCIATES
942110-00-0097/5/2017IV:3294903POWERMATIC ASSOCIATES
952110-00-0097/5/2017IV:3294904POWERMATIC ASSOCIATES
962110-00-0097/5/2017IV:3294905POWERMATIC ASSOCIATES
972110-00-0097/5/2017IV:7759035WES-GARDE COMPONENTS GROUP
Sheet1
 
Upvote 0
So it is the Col D that needs to separated?
Enter formula in E1 and copy across and down
Code:
[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
DEFG
1IV:9710388235LADD DISTRIBUTION LLCIV9710388235LADD DISTRIBUTION LLC
2IV:1300666392HARTING, INC. OF NORTH AMERICAIV1300666392HARTING, INC. OF NORTH AMERICA
3IV:ME1614-02MASTER ELECTRONICSIVME1614-02MASTER ELECTRONICS
4IV:ME1702-02MASTER ELECTRONICSIVME1702-02MASTER ELECTRONICS
5IV:MF3373-02MASTER ELECTRONICSIVMF3373-02MASTER ELECTRONICS
6IV:37761715MCMASTER-CARR SUPPLY COIV37761715MCMASTER-CARR SUPPLY CO
7IV:7759035WES-GARDE COMPONENTS GROUPIV7759035WES-GARDE COMPONENTS GROUP
Sheet2
 
Upvote 0
So it is the Col D that needs to separated?
Enter formula in E1 and copy across and down
Code:
[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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

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