How to split column in to two columns

[#1]

Hi,

I have Address column like below

32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP

How can I get 32 Beach Drive, Scratby, Great mouth, folk, UK in one column and UY29 3NP in another column.

can anyone help me please.

Thanks.

[#2]

Hi,  Thanks,

Could you please help me as I am trying to get the Address as parts

I,e could I get te second part and third part separately,

i,e Scartby as one column

Great nouth as another column .

Please help me this is the one I need to sort out please.

[marcus_malinow]

Hi Amelia,

probably the easiest solution is to use SubField.

For example SubField(Address, ',' 1) will retrieve the first part, SubField(Address, ',' 2) the second, etc...

You may wish to include an If condition around each so that if the subfield doesn't exist it doesn't error.

e.g.

If (SubstringCount(Address,',')>=1, SubField(Address, ',' 1), Null()) as FirstPart,

If (SubstringCount(Address,',')>=2, SubField(Address, ',' 2), Null()) as SecondPart,

etc...

Marcus

[rustyfishbones]

Amelia,

Just say SUBFIELD(Address,',',) to get them all separted

[#5]

Hi,

thanks.

I tried this but it is showing error at '1' place.

Please help me how to set this as I am really struggling this to get this done since days.

[#6]

Thanks and I used the SUBFIELD and it showing values incorrect order
Is there any other way to get this. Please this becomes now as big task for me. please help.

[marcus_malinow]

Aah, a couple of missing commas

If (SubstringCount(Address,',')>=1, SubField(Address, ',', 1), Null()) as FirstPart,

If (SubstringCount(Address,',')>=2, SubField(Address, ',', 2), Null()) as SecondPart,

[#8]

Thanks and I used the same and it's showing data when there is no data in second place. I need to have blank values if there is no data and values if there is data exists.

Please could help me how to set this.

[rustyfishbones]

If there are blank values or no data, these need to be separated by a comma, otherwise it won't work.

so if the data is like

32 Beach Drive, Scratby, Great mouth, folk, UK, UY29 3NP (this will be fine)

if it's like this

32 Beach Drive, , , , UK, UY29 3NP (it will also be fine)

however, if other addresses are like this

32 Beach Drive,folk, UK, UY29 3NP (it will not be fine)

you need to understand the Address field data first before you decide what function will be required to return the correct result

[#10]

Thanks and yes you are right I have most of the data as below

32 Beach Drive,folk, UK, UY29 3NP

so, in thus case how can I set this with out any issues. please help me.

[rustyfishbones]

Where is the Source Data coming from?

Excel, Access, or a Database?