Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Hi,
Thanks and I need
Address1 = 138 Elmsdale Avenue
Address2=
Address3=
Town=
County= Coventry
Postcode= CV6 6EU
Hi Amelia,
Please find the attached file for the solution.
Santhosh G
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.
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.
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.
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
Thanks and how can I select columns in chart item please help me