Delimiting by 2 different characters?

[sunnygilluk]

Hi all,

Im trying to format some data in excel that looks like this in A2:

05/06 00:00:00 AZT4512SD AA 000000 (ABC DEF) ZZ123456 1.1.1 0000 123 (ABC) A 1234 123456

from 1 cell into 1 for each "field" of data seperately (B2 = 05/06 C2 =00:00:00 etc)

Now normally i would delimit by space - however another record may also look like this:

05/06 00:00:00 AZT4512SD AA 000000 (ABC DEF GHI JKL) ZZ123456 1.1.1 0000 123 (ABC DEF) A 1234 123456

As you can see there are now more spaces in the items within the brackets.

I have found formulas to extract each piece of data (some fields have variable number of characters) by searching for text within spaces on first / second / third etc occurence and also within first / second brackets. However the formulas im using for characters between spaces go wrong once there are spaces within the brackets (after the brackets they become askew). Is there a way to do this and ignore what is in the brackets?

Thanks

 

[István Hirsch]

Copy the formula in B1 and copy down. Then copy column B and Paste Special as Values to column C. Select column C and use Text to Columns with space as a delimiter.

Excel Workbook
	A	B
1	05/06 00:00:00 AZT4512SD AA 000000 (ABC DEF GHI JKL) ZZ123456 1.1.1 0000 123 (ABC DEF) A 1234 123456	05/06 00:00:00 AZT4512SD AA 000000 ABC-DEF-GHI-JKL ZZ123456 1.1.1 0000 123 ABC-DEF A 1234 123456
2	05/06 00:00:00 AZT4512SD AA 000000 ABC DEF GHI JKL ZZ123456 1.1.1 0000 123 (ABC DEF) A 1234 123456	05/06 00:00:00 AZT4512SD AA 000000 ABC DEF GHI JKL ZZ123456 1.1.1 0000 123 ABC-DEF A 1234 123456
3	05/06 00:00:00 AZT4512SD AA 000000 ABC DEF GHI JKL ZZ123456 1.1.1 0000 123 ABC DEF A 1234 123456	05/06 00:00:00 AZT4512SD AA 000000 ABC DEF GHI JKL ZZ123456 1.1.1 0000 123 ABC DEF A 1234 123456
Sheet

 

[sunnygilluk]

Thanks for your solutions guys.

Managed to get what was required using the below for anyone who happens to be in a similar situation:

B2:
=TRIM(LEFT($A$2,FIND(" ",$A$2,1)-0))

C2:
=TRIM(RIGHT(LEFT($A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)),FIND(" ",$A$2,FIND(" ",$A$2,1)+1)-FIND(" ",$A$2,1)))

D2:
=TRIM(RIGHT(LEFT($A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)),FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)-FIND(" ",$A$2,FIND(" ",$A$2,1)+1)))

E2:
=TRIM(RIGHT(LEFT($A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)),FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)-FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)))

F2:
=TRIM(RIGHT(LEFT($A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)),FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)-FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)))

G2:
=TRIM(RIGHT(LEFT($A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)-1),FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)-FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)-1))

H2
=TRIM(RIGHT(LEFT($A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)),FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)-FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)))

I2
=TRIM(RIGHT(LEFT($A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)),FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)-FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)))

J2
=TRIM(RIGHT(LEFT($A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)),FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)-FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)))


K2
=TRIM(RIGHT(LEFT($A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)-1),FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)-FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)))

L2
=TRIM(RIGHT(LEFT($A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)-1),FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)-FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)-1))


M2
=TRIM(RIGHT(LEFT($A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)),FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)-FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)))


N2
=TRIM(RIGHT(LEFT($A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)),FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)-FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)))

O2
=TRIM(RIGHT(A2,LEN(A2)-FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(")",$A$2,FIND("(",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)))

 

[shinoobieeve]

*Tips hat.

You're Welcome

 

[sunnygilluk]

Thanks to this guy i might add!

 

[Rhodie72]

This is something that vba code could accomplish with ease and it could complete the lot in a few thousandths of a second too. If you were to paste a sample of data and express what portion does not change in length, then I may just have the code you need...

 

[István Hirsch]

No doubt, this is an absolutely unusual way of approaching this problem. By the way, how are you going to process cell A3?

 

[sunnygilluk]

I used the formulas above and replaced the $ with blanks. Then just dragged down. It will be used as a template to reformat data.

 

[FDibbins]

have not tried to recreate what you did in post 12, but if, instead of keep referring back to A2 in subsequent columns, consider using the "previous" find for the start of "this" find (if that made any sense at all?)

So you start with the base formula in B2
B2:
=TRIM(LEFT($A$2,FIND(" ",$A$2,1)-0))

Then in C2, search for B2's contents + B2's length as the start of the formula for C2
D2 finds C2 + C2's length for its starting point etc
=TRIM(RIGHT(LEFT($A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)),FIND(" ",$A$2,FIND(" ",$A$2,1)+1)-FIND(" ",$A$2,1)))

D2:
=TRIM(RIGHT(LEFT($A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)),FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)-FIND(" ",$A$2,FIND(" ",$A$2,1)+1)))

E2:
=TRIM(RIGHT(LEFT($A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)),FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)+1)-FIND(" ",$A$2,FIND(" ",$A$2,FIND(" ",$A$2,1)+1)+1)))

 

[István Hirsch]

Enter the formula in B1, then copy across and down:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	05/06 00:00:00 AZT4512SD AA 000000 (ABC DEF GHI JKL) ZZ123456 1.1.1 0000 123 (ABC DEF) A 1234 123456	05/06	00:00:00	AZT4512SD	AA	000000	ABC-DEF-GHI-JKL	ZZ123456	1.1.1	0000	123	ABC-DEF	A	1234	123456
2	05/06 00:00:00 AZT4512SD AA 000000 (ABC DEF GHI) ZZ123456 1.1.1 0000 123 (ABC) A 1234 123456	05/06	00:00:00	AZT4512SD	AA	000000	ABC-DEF-GHI	ZZ123456	1.1.1	0000	123	ABC	A	1234	123456
3	05/06 00:00:00 AZT4512SD AA 000000 (ABC DEF GHI JKL) ZZ123456 1.1.1 0000 123 (ABC DEF FFF GGG) A 1234 123456	05/06	00:00:00	AZT4512SD	AA	000000	ABC-DEF-GHI-JKL	ZZ123456	1.1.1	0000	123	ABC-DEF-FFF-GGG	A	1234	123456
Sheet