Extract data

[SimonHughes]

Hello,

I am using Excel 2010

I have data in column A (arround 5,000 entries) and I want to extract the product type and put it in column B whilst leaving the whole product data intact in A. The product names are not regularly formatted which causes the problem for me. I suspect it is a combination of find, mid etc but cannot work it out. Your help appreciated.

<TABLE style="WIDTH: 286pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=380><COLGROUP><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1645" width=45><COL style="WIDTH: 170pt; mso-width-source: userset; mso-width-alt: 8265" width=226><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 34pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17 width=45> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2f2f2; WIDTH: 170pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=226>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2f2f2; WIDTH: 82pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=109>B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 34pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=17 width=45 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c4d79b; WIDTH: 170pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=226>Product Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c4d79b; WIDTH: 82pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=109>Type</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 34pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=17 width=45 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 170pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=226>REVO F7 592X592X635 20H 10P</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 82pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=109>REVO</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 34pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=17 width=45 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 170pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=226>Fibatex F7 592X592X635 20H 10P</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 82pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=109>Fibatex</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; WIDTH: 34pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=17 width=45 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 170pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=226>E SONIQ 8 592X592X635 20H 10P</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 82pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=109>E SONIQ</TD></TR></TBODY></TABLE>

 

[Rick Rothstein]

Assuming that space after the text you want to extract is always there, give this formula a try....

=LEFT(A1,FIND(" ",A1&" ")-1)

This formula can be copied down through as many rows as you want (it does not matter if there is text in the Column A cell next to it or not).

 

[SimonHughes]

Hi Rick and thanks. It almost works - it just gives the E from the name ESONIQ but that is good enough as I can do a replace function.

Appreciated!

 

[Rick Rothstein]

Hi Rick and thanks. It almost works - it just gives the E from the name ESONIQ but that is good enough as I can do a replace function.

I'm sorry... I completely missed the space in that name. That is a tough text string to parse. If there is always at least one digit between the first space after the company name (no matter how many spaces the company name contains) and the space after that one, and as long as the company name does not have any digits in it, then this formula should work for you...

=TRIM(SUBSTITUTE(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))),TRIM(RIGHT(SUBSTITUTE(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))," ",REPT(" ",99)),99)),""))

 

[Peter_SSs]

The examples all have four 'words' after the required product type. If that is consistent then maybe this?

=LEFT(A2,FIND("#",SUBSTITUTE(A2," ","#",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-3))-1)

 

[SimonHughes]

Hi Rick/Peter,

Peter's solution works perfectly and many thanks. Rick - your solution piick up some but not all unfortunately - but what a formula !

Anyway, many thanks to you both, I have what I need and it's appreciated.