Extracting from a column

[MiguelS]

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]

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]

Try:

Excel 2013/2016
	A	B	C	D
1	IV:9710388235LADD DISTRIBUTION LLC	IV:	9710388235	LADD DISTRIBUTION LLC
Sheet1

Cell Formulas
Range	Formula
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)

 

[Rick Rothstein]

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]

Something a little more dynamic in case the string before the numbers varies in length:

Excel 2013/2016
	A	B	C	D
1	IV:9710388235LADD DISTRIBUTION LLC	IV:	9710388235	LADD DISTRIBUTION LLC
Sheet1

Cell Formulas
Range	Formula
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)

 

[Fluff]

How about
pinched the lookup formula from
Find Last Number Within String
Sheet2

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

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]9710388235[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]97103835[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]971038823599[/TD]

</tbody>

Spreadsheet Formulas

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]

Sorry, the length is not consistent. I will try these solutions posted...thanks.

 

[Rick Rothstein]

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]

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]

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)