subtrac string values from a text and create three extra column with the info extracted

**mils** · 10-22-2015, 07:44 AM

Hi,

I have a column with the following format (this is a short example):

Can Al SOL 355ml Exp 2014
Can Al 7UP 330ml STD Vintage 50s 2015
ZZ_Can Al HEINEKEN Light 500ml 473 US 13
ZZ_Can Al TECATE 710ml MX 2009
Can Al SOL 355ml Carnaval 2012
Can Al HEINEKEN 330ml 4x6 NL 1508
ZZ_Can Al CARTA BLANCA Light 355ml 2010
Can Al CARTA BLANCA 355ml CL/CO 2012
Can Al STRONGBOW Red Berries 400BG/GR/RO

I need to create three extra columns with: Brand, capacity and description

Brand Capacity Description
SOL 355ml Exp 2014
7UP 330ml STD Vintage 50s 2015
HEINEKEN Light 500ml 473 US 13
TECATE 710ml MX 2009
SOL 355ml Carnaval 2012
HEINEKEN 330ml 4x6 NL 1508
CARTA BLANCA Light 355ml 2010
CARTA BLANCA 355ml CL/CO 2012
STRONGBOW Red Berries 400BG/GR/RO

I was wondering if it is possible to use a formula(s) to subtract/extract the information/values needed.

Please see attached file which illustrates what I need.

example.xlsx

Please, let me know if you need anything else!

Thanks in advance!

**bebo021999** · 10-22-2015, 08:04 AM

B2:

=TRIM(MID($A2,SEARCH("Can Al",$A2)+6,IFERROR(SEARCH("ml",$A2),SEARCH("BG/",$A2))-SEARCH("Can Al",$A2)-9))

C2:

=IFERROR(TRIM(RIGHT(LEFT($A2,SEARCH("ml",$A2)-1),4)),"")

D2:

=TRIM(MID($A2,IFERROR(SEARCH("ml",$A2),SEARCH("BG/",$A2)-6)+2,255))

**mils** · 10-22-2015, 09:35 AM

Hi Quang PT,

thanks for your code, it does work perfectly!!!!;-)

I just realize that I also need the type. Some beers are: light, free, gold, etc.

I have attached the file again to illustrate what I mean.

Thanks a lot for your help!

example.xlsx

**mils** · 10-22-2015, 10:38 AM

Hi, does anyone know a function to identify uppercase words?

In my example the brands are always uppercase.

**bebo021999** · 10-23-2015, 12:50 AM

I am getting closer, but type is not identified. Do you have a complete list of types?

**mangesh.mehendale** · 10-23-2015, 03:42 AM

Done...

For Brand name... other things are already tackle by Sir bebo021999..

[/formula]
=IF(EXACT(UPPER(SUBSTITUTE(LEFT(A16,FIND(CHAR(1),SUBSTITUTE(A16," ",CHAR(1),4))),LEFT(A16,FIND(CHAR(1),SUBSTITUTE(A16," ",CHAR(1),2))),"")),SUBSTITUTE(LEFT(A16,FIND(CHAR(1),SUBSTITUTE(A16," ",CHAR(1),4))),LEFT(A16,FIND(CHAR(1),SUBSTITUTE(A16," ",CHAR(1),2))),"")),SUBSTITUTE(LEFT(A16,FIND(CHAR(1),SUBSTITUTE(A16," ",CHAR(1),4))),LEFT(A16,FIND(CHAR(1),SUBSTITUTE(A16," ",CHAR(1),2))),""),SUBSTITUTE(LEFT(A16,FIND(CHAR(1),SUBSTITUTE(A16," ",CHAR(1),3))),LEFT(A16,FIND(CHAR(1),SUBSTITUTE(A16," ",CHAR(1),2))),""))
[/formula]

**mils** · 10-23-2015, 04:52 AM

Thanks for your response! I'm getting there!

I have now a list of brands, types and capacities. I would like to double check that I'm getting everything.

I would like to create a vlookup. This should look at column Product description and check if the brand (from my list) exists, if so paste the brand next to it:

ProductDescription BrandCheck
SOL 355ml Exp 2014 SOL
NekeiehKENIEH Light 500ml FALSE
HEINEKEN Light 500ml HEINEKEN

This way I can identify which brands I'm missing.

Thanks for your help

Book2.xlsx

**Glenn Kennedy** · 10-23-2015, 05:03 AM

I doubt very much if your list of examples is truly representative... but this works for the examples given...

Brand

Formula:

=LEFT(MID(SUBSTITUTE(A2," ","|",2),FIND("|",SUBSTITUTE(A2," ","|",2))+1,255),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MID(MID(SUBSTITUTE(A2," ","|",2),FIND("|",SUBSTITUTE(A2," ","|",2))+1,255),2,255)&"0123456789"))-1)

Pack size

Formula:

=IFERROR(TRIM(LEFT(TRIM(SUBSTITUTE(MID(SUBSTITUTE(A2," ","|",2),FIND("|",SUBSTITUTE(A2," ","|",2))+1,255),B2,"")),FIND(" ",TRIM(SUBSTITUTE(MID(SUBSTITUTE(A2," ","|",2),FIND("|",SUBSTITUTE(A2," ","|",2))+1,255),B2,"")))-1)),"")

Description

Formula:

=TRIM(SUBSTITUTE(TRIM(SUBSTITUTE(MID(SUBSTITUTE(A2," ","|",2),FIND("|",SUBSTITUTE(A2," ","|",2))+1,255),B2,"")),IFERROR(TRIM(LEFT(TRIM(SUBSTITUTE(MID(SUBSTITUTE(A2," ","|",2),FIND("|",SUBSTITUTE(A2," ","|",2))+1,255),B2,"")),FIND(" ",TRIM(SUBSTITUTE(MID(SUBSTITUTE(A2," ","|",2),FIND("|",SUBSTITUTE(A2," ","|",2))+1,255),B2,"")))-1)),""),""))

**bebo021999** · 10-23-2015, 05:42 AM

@Glen
I afraid that your solution does not meet his latest requirement in #3

**sam1105** · 10-23-2015, 05:46 AM

I think both r same..

http://www.excelforum.com/excel-gene...in-a-text.html

**Glenn Kennedy** · 10-23-2015, 05:56 AM

Bebo... Damn and blast!!! I missed that bit.

**samba_ravi** · 10-23-2015, 06:30 AM

B2=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),1+2*LEN(A2),AND(ABS(109.5-CODE(LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),1+3*LEN(A2),LEN(A2))),1)))>12.5,ABS(109.5-CODE(RIGHT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),1+3*LEN(A2),LEN(A2))),1)))>12.5)*LEN(A2)+LEN(A2)))

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),1+2*LEN(A2),AND(ABS(109.5-CODE(LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),1+3*LEN(A2),LEN(A2))),1)))>12.5,ABS(109.5-CODE(RIGHT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),1+3*LEN(A2),LEN(A2))),1)))>12.5)*LEN(A2)+LEN(A2)))

D2=IFERROR(TRIM(RIGHT(SUBSTITUTE(REPLACE(A2,SEARCH("ml",A2)+2,256,)," ",REPT(" ",50)),50)),"")

=IFERROR(TRIM(RIGHT(SUBSTITUTE(REPLACE(A2,SEARCH("ml",A2)+2,256,)," ",REPT(" ",50)),50)),"")

E2=IFERROR(TRIM(IF(D2="",RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50),REPLACE(A2,1,SEARCH("ml",A2)+2,))),"")

=IFERROR(TRIM(IF(D2="",RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50),REPLACE(A2,1,SEARCH("ml",A2)+2,))),"")

C2=TRIM(SUBSTITUTE(SUBSTITUTE(REPLACE(A2,1,SEARCH(B2,A2)+LEN(B2),),D2,""),E2,""))

=TRIM(SUBSTITUTE(SUBSTITUTE(REPLACE(A2,1,SEARCH(B2,A2)+LEN(B2),),D2,""),E2,""))

I hope some one come with better formula, I am eagerly waiting for that
see the attached file

**mils** · 10-23-2015, 07:07 AM

Wow, that is a great solution!!!!

Thanks to all of you!!!

**bebo021999** · 10-23-2015, 10:37 AM

Nice to hear it works with nfsales. Beside formula in B2 appears complicated, it does not work with brand name with 3 groups of upper charaters
like this:
solve: ZZ_Can Al CARTA BLANCA Light 355ml 2010
not solve: ZZ_Can Al CARTA BLANCA ABC Light 355ml 2010

**samba_ravi** · 10-24-2015, 07:44 AM

Originally Posted by bebo021999

Nice to hear it works with nfsales. Beside formula in B2 appears complicated, it does not work with brand name with 3 groups of upper charaters
like this:
solve: ZZ_Can Al CARTA BLANCA Light 355ml 2010
not solve: ZZ_Can Al CARTA BLANCA ABC Light 355ml 2010

it this case use below formula
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),2*LEN(A2)+1,MIN(FIND({"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","v","w","x","y","z"},SUBSTITUTE(A2," ",REPT(" ",LEN(A2)))&"abcdefghijklmnopqrstuvwxyz",2*LEN(A2)+1))-3*LEN(A2)))

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),2*LEN(A2)+1,MIN(FIND({"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","v","w","x","y","z"},SUBSTITUTE(A2," ",REPT(" ",LEN(A2)))&"abcdefghijklmnopqrstuvwxyz",2*LEN(A2)+1))-3*LEN(A2)))

**FlameRetired** · 10-25-2015, 10:01 PM

I tried another approach ... backwards one solving Capacity (D2:D10) first, then Description (E2:E10). Then went to B2:B10 and C2:C10. Untidy approach but it seems easier and allowed for shorter formulas than I was previously getting.

In D2 and down this:

Formula:

=IFERROR(MID($A2,LOOKUP(25^25,FIND(ROW($100:$999)&"ml ",$A2)),5),"")

In E2 and down this:

Formula:

=TRIM(RIGHT(IF(D2<>"",REPLACE(A2,FIND(D2,A2),LEN(D2),REPT(" ",99)),REPLACE(A2,1,LOOKUP(25^25,FIND(" ",A2,ROW($1:$50))),"")),99))

In B2 and down this:

Formula:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Can Al ",""),"ZZ_",""),D2&" "&E2,""),"Light ",""),"Red Berries",""))

and in E2 and down this:

Formula:

=TRIM(SUBSTITUTE(SUBSTITUTE(REPLACE(A2,1,FIND(B2,A2)-1,""),D2&" "&E2,""),B2,""))

File is attached.

**samba_ravi** · 10-26-2015, 12:53 AM

Originally Posted by FlameRetired

I tried another approach ... backwards one solving Capacity (D2:D10) first, then Description (E2:E10). Then went to B2:B10 and C2:C10. Untidy approach but it seems easier and allowed for shorter formulas than I was previously getting.

In D2 and down this:

Formula:

=IFERROR(MID($A2,LOOKUP(25^25,FIND(ROW($100:$999)&"ml ",$A2)),5),"")

In E2 and down this:

Formula:

=TRIM(RIGHT(IF(D2<>"",REPLACE(A2,FIND(D2,A2),LEN(D2),REPT(" ",99)),REPLACE(A2,1,LOOKUP(25^25,FIND(" ",A2,ROW($1:$50))),"")),99))

In B2 and down this:

Formula:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Can Al ",""),"ZZ_",""),D2&" "&E2,""),"Light ",""),"Red Berries",""))

and in E2 and down this:

Formula:

=TRIM(SUBSTITUTE(SUBSTITUTE(REPLACE(A2,1,FIND(B2,A2)-1,""),D2&" "&E2,""),B2,""))

File is attached.

Instead of =IFERROR(MID($A2,LOOKUP(25^25,FIND(ROW($100:$999)&"ml ",$A2)),5),"") can use
=IFERROR(MID($A2,LOOKUP(25^25,FIND(ROW($1:$9)&"??ml ",$A2)),5),"")

**FlameRetired** · 10-26-2015, 01:35 AM

Originally Posted by nflsales

Instead of =IFERROR(MID($A2,LOOKUP(25^25,FIND(ROW($100:$999)&"ml ",$A2)),5),"") can use
=IFERROR(MID($A2,LOOKUP(25^25,FIND(ROW($1:$9)&"??ml ",$A2)),5),"")

Yes. Better. Thanks for the backup.

Edit @nflsales: FIND needs to be replaced w/ SEARCH. FIND won't allow wildcard characters.

**FlameRetired** · 10-25-2015, 10:59 PM

Double posted.

subtrac string values from a text and create three extra column with the info extracted

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

subtrac string values from a text and create three extra column with the info extracted

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Re: subtrac string values from a text and create three extra column with the info extracte

Thread Information

Users Browsing this Thread

Similar Threads

Taking a string of text and adding extra chars, for a column.

Create VBA that types text into a column if target cell contains certain string

[SOLVED] SUMIF Query: How to summarise values based on data extracted from a text string

[SOLVED] Time values from extracted text strings

[SOLVED] Text and number Data to be parsed ( extracted ) from a single column

[SOLVED] getting Excel to create an extra column

[SOLVED] Can delimited text file imports be extracted to a single column i.

Bookmarks

Bookmarks

Posting Permissions