Extracting from a column

M

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.
 
Sort by date Sort by votes
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
MiguelS said:
yes. The other issue I have when I tested your formulas was with row 17
Click to expand...
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
MiguelS said:
No, but I am sure it is 85% of the time.
Click to expand...
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
AlKey said:
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]
Click to expand...
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
You must log in or register to reply here.

Similar threads

B
  • Question Question
Formula(s) or VBA to re-distribute PTO hour balance
Replies
1
Views
247
bh24524
B
A
  • Solved
VBA code to extract a table
Replies
5
Views
163
Anthony47
A
N
  • Question Question
One common pattern to extract Numerical Values using Regular Expressions
Replies
5
Views
559
John_w
J
J
  • Question Question
VB Macro for extracting Excel email address data to Outlook email draft
Replies
0
Views
854
Jaydee12
J
E
  • Solved
Formula for extracting multiple occurrences of text substring in a cell
2
Replies
19
Views
887
Sektor
Sektor

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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