# Extracting from a column

#### MiguelS

##### New Member

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

Try:

Last edited:

#### Rick Rothstein

##### MrExcel MVP

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
Something a little more dynamic in case the string before the numbers varies in length:

#### Fluff

##### MrExcel MVP, Moderator
pinched the lookup formula from
Find Last Number Within String
Sheet2

 A B C D 1 IV:9710388235LADD DISTRIBUTION LLC IV: 9710388235 LADD DISTRIBUTION LLC 2 IV:97103835LADD DISTRIBUTION IV: 97103835 LADD DISTRIBUTION 3 IV:971038823599MrExcell IV: 971038823599 MrExcell

<tbody>
</tbody>

 Cell Formula 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
Sorry, the length is not consistent. I will try these solutions posted...thanks.

#### Rick Rothstein

##### MrExcel MVP
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
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
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)

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

### 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...