extract from a string.

**rakun** · 03-02-2011, 12:32 PM

I need to take out parts from a string, and place them in different cells.
String as follow
dfd://aas:hadd3a8py@dsdd.usuos.cos.es/ rrtt://aas.csd-wwb.ew/
dfd://georg:hnk738py@dfd.usuos.cos.es/ rrtt://georg.csd-wwb.ew/

need to put in cell1 aas, cell2 hadd3a8py, cell3 dsdd.usuos.cos.es cell4 rrtt://georg.csd-wwb.ew

The same for the second string.

Please note that words could be small and long only thing that are the same are separators.

How to cut these? Please help.
Thank you.

**NBVC** · 03-02-2011, 12:53 PM

Try:

=MID(A1,FIND("//",A1)+2,FIND(":",A1,FIND("//",A1))-FIND("//",A1)-2)

=LEFT(MID(A1,FIND(B1&":",A1)+LEN(B1)+1,255),FIND("@",MID(A1,FIND(B1&":",A1)+LEN(B1)+1,255))-1)

=LEFT(MID(A1,FIND(C1&"@",A1)+LEN(C1)+1,255),FIND("/",MID(A1,FIND(C1&"@",A1)+LEN(C1)+1,255))-1)

=LEFT(MID(A1,FIND(D1&"/",A1)+LEN(D1)+2,255),LEN(MID(A1,FIND(D1&"/",A1)+LEN(D1)+2,255))-1)

in B1:D1, respectively, and copy down

**rakun** · 03-03-2011, 08:32 AM

Oh wow Thank you so MUCH!!!

**rakun** · 03-03-2011, 08:40 AM

I see that each next string depends on previous. I need that each uses A1and don't depend on others.
Could you do that please?

**NBVC** · 03-03-2011, 08:52 AM

Why can't they depend on previous?

**MarvinP** · 03-03-2011, 09:25 AM

Hi rakun,
Be careful what you ask for. Put your string in A1 and the following in B1 and drag the formula to the right.
See if this gives you the results you want.

=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,"//"," "),":"," "),"@"," "))," ",REPT(" ",LEN(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,"//"," "),":"," "),"@"," "))))),LEN(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,"//"," "),":"," "),"@"," ")))*(COLUMN()-1),LEN(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,"//"," "),":"," "),"@"," ")))))

As I saved the answer it said that Excel 2003 will not support more than 7 levels of nesting. So here is your formula that will work in a single cell but you need to upgrade to a newer version of Excel.

I like NBVC's answer better. Why can't it depend on previous? My formula above depends on the column the formula is in, so previous makes sense to me.