Hi , Having data like this , how to make the data as below screen shot. Input LocationIDDeptMembersSP1ScienceA1 - 34543 A1 - 56789MA2MathsB1 - 876649 A1 - 8423423 C1 - 324234MT3ArtsD1 - 543542 F1 - 254524GO4EngineeringS1 - 53254GA5SocialP1 - 412412 D - 4134141 Result LocationIDDeptMembersSP1ScienceA1 - 34543SP1ScienceA1 - 56789MA2MathsB1 - 876649MA2MathsA1 - 8423423MA2MathsC1 - 324234MT3ArtsD1 - 543542MT3ArtsF1 - 254524GO4EngineeringS1 - 53254GA5SocialP1 - 412412GA5SocialD - 4134141 Please let me know to transform data like that. Thanks

Need help with preperation of data

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

Transformation

Preparation

Accepted answers

vishwa_0308

Use this expression:

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

Thanks,

Vishwa

All comments

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.

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703