Need help with preperation of data

[shankar12]

Hi ,

Having data like this , how to make the data as below screen shot.

Input 

Location	ID	Dept	Members
SP	1	Science	A1 - 34543 A1 - 56789
MA	2	Maths	B1 - 876649 A1 - 8423423 C1 - 324234
MT	3	Arts	D1 - 543542 F1 - 254524
GO	4	Engineering	S1 - 53254
GA	5	Social	P1 - 412412 D - 4134141

Result

Location	ID	Dept	Members
SP	1	Science	A1 - 34543
SP	1	Science	A1 - 56789
MA	2	Maths	B1 - 876649
MA	2	Maths	A1 - 8423423
MA	2	Maths	C1 - 324234
MT	3	Arts	D1 - 543542
MT	3	Arts	F1 - 254524
GO	4	Engineering	S1 - 53254
GA	5	Social	P1 - 412412
GA	5	Social	D - 4134141

Please let me know to transform data like that.

Thanks

[vishwa_0308]

@shankar12,

use the below expression in the regex tool on members field

\w\d\s\-\s\w+

Output method : Tokenize  then split to rows.

Thanks,

Vishwa

[shankar12]

Location	ID	Dept	Members
SP	1	Science	A1 34543 A1 -56789
MA	2	Maths	B1 - 876649 A1 - 8423423 C1 - 324234
MT	3	Arts	D1 - 543542 - F1-254524
GO	4	Engineering	S1 - 53254
GA	5	Social	P1 -412412 D- 4134141
Result
Location	ID	Dept	Members
SP	1	Science	A1 - 34543
SP	1	Science	A1 - 56789
MA	2	Maths	B1 - 876649
MA	2	Maths	A1 - 8423423
MA	2	Maths	C1 - 324234
MT	3	Arts	D1 - 543542
MT	3	Arts	F1 - 254524
GO	4	Engineering	S1 - 53254
GA	5	Social	P1 - 412412
GA	5	Social	D - 4134141

Thanks viswa your solution helped me partially, i still have some problem the "-" and the spaces are  irregular.is there any solution for thsi.

Thanks.

[vishwa_0308 · Accepted solution]

Use this expression:

\w*\s*\-\s*\w+

Thanks,

Vishwa

[MarqueeCrew]

When I look at the data I see a space followed by a letter as the key. \s\u wants to be replaced with a | symbol.

Then it would be easy to split to rows.

Cheers,

Mark

[shankar12]

Got it.Thanks Vishwa.