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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
Try:


Excel 2013/2016
ABCD
1IV:9710388235LADD DISTRIBUTION LLCIV:9710388235LADD DISTRIBUTION LLC
Sheet1
Cell Formulas
RangeFormula
B1=LEFT(A1,3)
C1=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$24),1))* ROW($1:$24),0),ROW($1:$24))+1,1)*10^ROW($1:$24)/10)
D1=RIGHT(A1,LEN(A1)-SEARCH(C1,A1)-LEN(C1)+1)
 
Last edited:
Upvote 0
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?
 
Upvote 0
Something a little more dynamic in case the string before the numbers varies in length:


Excel 2013/2016
ABCD
1IV:9710388235LADD DISTRIBUTION LLCIV:9710388235LADD DISTRIBUTION LLC
Sheet1
Cell Formulas
RangeFormula
B1=LEFT(A1,SEARCH(C1,A1)-1)
C1=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$24),1))* ROW($1:$24),0),ROW($1:$24))+1,1)*10^ROW($1:$24)/10)
D1=RIGHT(A1,LEN(A1)-SEARCH(C1,A1)-LEN(C1)+1)
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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