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.
 

AKR22

New Member
Joined
Jul 13, 2016
Messages
39
Im assuming the value in the original column is consistent in number of characters:

Column 1: =LEFT(A1,3)

Column 2: =MID(A1,4,10)

Column 3: =RIGHT(A1,21)

Change "A1" to wherever the orginal value is.

Cheers,

AKR22
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,649
Try:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">IV:9710388235LADD DISTRIBUTION LLC</td><td style=";">IV:</td><td style=";">9710388235</td><td style=";">LADD DISTRIBUTION LLC</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=LEFT(<font color="Blue">A1,3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">MID(<font color="Red">0&A1,LARGE(<font color="Green">INDEX(<font color="Purple">ISNUMBER(<font color="Teal">--MID(<font color="#FF00FF">A1,ROW(<font color="Navy">$1:$24</font>),1</font>)</font>)* ROW(<font color="Teal">$1:$24</font>),0</font>),ROW(<font color="Purple">$1:$24</font>)</font>)+1,1</font>)*10^ROW(<font color="Red">$1:$24</font>)/10</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">=RIGHT(<font color="Blue">A1,LEN(<font color="Red">A1</font>)-SEARCH(<font color="Red">C1,A1</font>)-LEN(<font color="Red">C1</font>)+1</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,510
Office Version
2010
Platform
Windows
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.
Is the text before the colon always two characters long?

Is the number always ten digits long?
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,649
Something a little more dynamic in case the string before the numbers varies in length:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">IV:9710388235LADD DISTRIBUTION LLC</td><td style=";">IV:</td><td style=";">9710388235</td><td style=";">LADD DISTRIBUTION LLC</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=LEFT(<font color="Blue">A1,SEARCH(<font color="Red">C1,A1</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">MID(<font color="Red">0&A1,LARGE(<font color="Green">INDEX(<font color="Purple">ISNUMBER(<font color="Teal">--MID(<font color="#FF00FF">A1,ROW(<font color="Navy">$1:$24</font>),1</font>)</font>)* ROW(<font color="Teal">$1:$24</font>),0</font>),ROW(<font color="Purple">$1:$24</font>)</font>)+1,1</font>)*10^ROW(<font color="Red">$1:$24</font>)/10</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">=RIGHT(<font color="Blue">A1,LEN(<font color="Red">A1</font>)-SEARCH(<font color="Red">C1,A1</font>)-LEN(<font color="Red">C1</font>)+1</font>)</td></tr></tbody></table></td></tr></table><br />
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,332
Office Version
365
Platform
Windows
How about
pinched the lookup formula from
Find Last Number Within String
Sheet2

ABCD
1IV:9710388235LADD DISTRIBUTION LLCIV:9710388235LADD DISTRIBUTION LLC
2IV:97103835LADD DISTRIBUTIONIV:97103835LADD DISTRIBUTION
3IV:971038823599MrExcellIV:971038823599MrExcell

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B1=LEFT(A1,3)
C1=MID(A1,4,LOOKUP(1,-MID(A1,ROW($1:$64),1),ROW($1:$64))-3)
D1=RIGHT(A1,LEN(A1)-LOOKUP(1,-MID(A1,ROW($1:$64),1),ROW($1:$64)))
B2=LEFT(A2,3)
C2=MID(A2,4,LOOKUP(1,-MID(A2,ROW($1:$64),1),ROW($1:$64))-3)
D2=RIGHT(A2,LEN(A2)-LOOKUP(1,-MID(A2,ROW($1:$64),1),ROW($1:$64)))
B3=LEFT(A3,3)
C3=MID(A3,4,LOOKUP(1,-MID(A3,ROW($1:$64),1),ROW($1:$64))-3)
D3=RIGHT(A3,LEN(A3)-LOOKUP(1,-MID(A3,ROW($1:$64),1),ROW($1:$64)))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

MiguelS

New Member
Joined
Apr 8, 2016
Messages
18
Sorry, the length is not consistent. I will try these solutions posted...thanks.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,510
Office Version
2010
Platform
Windows
Sorry, the length is not consistent.
Okay, then here is another solution BUT it will only work so long as there are no digits in the text after the number you are wanting to pull out (so if you could have a company name like GLASSES4YOU, then it won't work). Assuming your text is in cell A1, put these formulas in the indicated cells and copy down as needed...

B1: =LEFT(A1,FIND(":",A1)-1)

C1: =MID(A1,FIND(":",A1)+1,MAX(FIND({0,1,2,3,4,5,6,7,8,9},"0123456789"&A1))-FIND(":",A1)+3)

D1: =MID(A1,MAX(FIND({0,1,2,3,4,5,6,7,8,9},"0123456789"&A1)+4),99)
 

MiguelS

New Member
Joined
Apr 8, 2016
Messages
18
Okay, then here is another solution BUT it will only work so long as there are no digits in the text after the number you are wanting to pull out (so if you could have a company name like GLASSES4YOU, then it won't work). Assuming your text is in cell A1, put these formulas in the indicated cells and copy down as needed...

B1: =LEFT(A1,FIND(":",A1)-1)

C1: =MID(A1,FIND(":",A1)+1,MAX(FIND({0,1,2,3,4,5,6,7,8,9},"0123456789"&A1))-FIND(":",A1)+3)

D1: =MID(A1,MAX(FIND({0,1,2,3,4,5,6,7,8,9},"0123456789"&A1)+4),99)

Yes, I noticed that it didnt work with ALPHANUMERIC, but we can live with it. I am trying to posting the data in table format, but my MrExcelHTML does not work,

I am also trying to figure out how to change it if my data is at D1. Changing A1 to D1 is simple enough, but I am sure I am missing a parameters somewhere.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,510
Office Version
2010
Platform
Windows
Yes, I noticed that it didnt work with ALPHANUMERIC, but we can live with it.
You don't have to "live with it"... you just have to make sure you tell us what kind of data you actually have so we can account for it.
[/QUOTE]
Give these new formulas a try (I have moved the cell reference to D1 for you and moved the output to cells E1, F1 and G1)...

E1: =LEFT(D1,FIND(":",D1)-1)

F1: =MID(D1,FIND(":",D1)+1,MIN(SEARCH({"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"},D1&"abcdefghijklmnopqrstuvwxyz",FIND(":",D1)))-FIND(":",D1)-1)

G1: =MID(D1,MIN(SEARCH({"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"},D1&"abcdefghijklmnopqrstuvwxyz",FIND(":",D1))),99)
 

Forum statistics

Threads
1,082,367
Messages
5,365,028
Members
400,819
Latest member
Gossow

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top