How to remove characters in between a string.

[Jesh19]

Hi All,

I am having a requirement where i need to cleanse my data.

I know the usage of purgechar and i have tried it, but of no use.

Could you please suggest any other possible alternative for the below mentioned requirement.

My requirement is something like this:

I am having a department field with values

Department                                            Expected Outcome

9989504213 - A2B                                        A2B

Other (123456789)                                        Other

Please do the needful.

Thanks in advance.

Regards,

Jeshwanth

[ajsjoshua]

Hi,

Try this

trim(subfield(subfield(9989504213 - A2B  , '-', 2),'(', 2))

Regards,

Joshua.

[rahulpawarb]

Hello Jeshwanth,

Trust that you are doing well!

I have few queries;

1. Field Department consists of two parts. In given examples, '-' and ' ' are used as separators. What all other separators available under Department field?

2. You are willing to remove numbers - plain or surrounded by round brackets. What is the position of such numbers (First part of string or Second part)?

3. Numbers part of string will be kept intact. Is it correct?

Answers to these questions will help us to provide you expected feedback.

Regards!

Rahul

[Anil_Babu_Samineni]

I am not sure, Can you send real data i mean atleast 10 values from Department

If less than expected values you can go with below

LOAD Department,If(Index(Department,'A2B'),'A2B',If(Index(Department,'Other'),'Other')) as Department1 Inline [

Department

9989504213 - A2B

Other (123456789)

];

[avinashelite]

We need a pattern or position to Identify the words, other wise it would difficult to handle this ...Ask you data team have it in proper format so that we could split the values 

[Jesh19 · Accepted solution]

Hello Eveyone,

Thanks for your help.

I got the solution.

I have tried something like this and it worked out very well.

Load * Where Not( IsNum(Department)) and Ord(Department) <> 0;

Load

     SubField(PurgeChar(SubField(Department,'-'),'()'),' ') as Department.

Regards,

Jeshwanth B