SubField Function Struggle

[dineshm030]

Hi All,

I want to take separate column (Distributor Name) from which is copied  below the screenshot.

I took Distributor Code by this expression:

SubField([Distributor Name & Code],' ',1) as Distributor_Code

[micheledenardi]

Try this solution:

Distributor:
load 
trim(replace(Distributor,Left(Distributor,Index(Distributor,' ')-1),'')) as [Distributor Description],
Left(Distributor,Index(Distributor,' ')-1) as [Distributor Code],
Distributor
Inline [
Distributor
00123 Dist1
56    Dist2
598215689 Dist3
1 Dist4
458 Dist5];

And the result is:

[sagar_puri]

Hi Dinesh,

Your expression is absolutely correct only issue is the delimiter spacing.

I tried out using  SubField(Distributor,'  ',1) as testvalue  and got the required result.

Regards,

[dineshm030]

I took Distributor code.

I want to take like this

stalwar1‌

[jayshrinipurte]

hi Dinesh,

Try this,

SubField([Distributor Name & Code],'  ',2) as Distributor_Code

Regards,

Jayshri

[sagar_puri]

Hi ,

Try the expression shared by jayshri it works fine.

[micheledenardi · Accepted solution]

My solution works as you want !

trim(replace([Distributor Name & Code],Left([Distributor Name & Code],Index([Distributor Name & Code],' ')-1),'')) as [Distributor Name], 

Left([Distributor Name & Code],Index([Distributor Name & Code],' ')-1) as [Distributor Code]

[sunny_talwar]

How about this

Trim(Mid([Distributor Name & Code], Index([Distributor Name & Code], ' '))) as [Distributor Name]