How to get one column value split in to multiple columns

[#1]

Hi,

Could anyone help me how can I split the Home values in to

Address1, Address2, Address3, Town ,County, Postcode.

Please find attached. I have been using SUBFIELD

=SUBFIELD(Home,',',1)

=If(Address2<>Postcode,SUBFIELD(Home,',',-1))

=If(Address3<>Postcode,SUBFIELD(Home,',',-2))

=If(Town<>Postcode,SUBFIELD(Home,',',-3))

=If(County<>Postcode,SUBFIELD(Home,',',-4))

=SUBFIELD(Home,',',-1)

but it showing incorrect values
Please any solution to sort this problem.

Thanks.

[#2]

Hi Amelia,

You can use Subfield function to get the desired result. Can you explain, in:

138 Elmsdale Avenue, Coventry, CV6 6EU

What is it that you want as Address1, Address2 etc.

[#3]

Hi,

Thanks and I need

Address1 = 138 Elmsdale Avenue

Address2=

Address3=

Town=

County= Coventry

Postcode= CV6 6EU

[#4]

Hi Amelia,

  Please find the attached file for the solution.

Santhosh G

[#5]

Hi Amelia,

I can see that there is an inconsistency in the delimiter position, in order to be able to use subfield function, you should have delimiters at fixed positions.

[christian77]

Have I seen this before?

The same question buy the same person.

The answer is the same.

Free texts are difficult to treat and analize.

[#7]

Is there any solution to fix this because that's how I have data in SQL table. Any solution please with out changing the delimiter position.

[Anonymous]

Hi Amelia,

  i written below script for Address Calculation,but problem is some  CamCount=4 then City is coming in 3rd position,other please it's coming in 4th Place,below script is failure those conditions,CamCount=8,i didn't under stand.how i want to take.

Flat 68, Arundel Court, London, 0 Lansdowne Road, N17 0LR

Directory;

load *,

     if(CamCount=2,SubField(Home,',',1),

     if(CamCount=3,SubField(Home,',',1),

     if(CamCount=4,SubField(Home,',',1)))) AS Address1,

     if(CamCount=3,SubField(Home,',',2),

     if(CamCount=4,SubField(Home,',',2)))  AS Address2,

     if(CamCount=4,SubField(Home,',',3))   AS Address3,

     if(CamCount=2,SubField(Home,',',2),

     if(CamCount=3,SubField(Home,',',3),

     if(CamCount=4,SubField(Home,',',4)))) AS City,

     if(CamCount=2,SubField(Home,',',3),

     if(CamCount=3,SubField(Home,',',4),

     if(CamCount=4,SubField(Home,',',5)))) AS PinCode;

LOAD

  Home,

  SubStringCount(Home,',') AS CamCount

// SubField(Home,',',1) As Address1,

// SubField(Home,',',2) As Address2,

// SubField(Home,',',3) As Address3,

// SubField(Home,',',4) As Address4

FROM

Address_Person.xlsx

(ooxml, embedded labels, table is Sheet1);

Thanks

SHAIK

[#9]

Thanks and how can I select columns in chart item please help me