Address transformation VBA --- splitting

[Susy]

Hi there,

I am getting a lot addresses with c/o at the end of them.



However I need to split them. For example: Main Street 24 c/o John Smith should be like this:

Column1-->Address: Main Street 24 c/o John Smith
Column2-->Address1: Main Street 24
Column3--->Address2: c/o John Smith

Sometimes I also get things like this: Main Street 24, so:

Column1-->Address: Main Street 24
Column2-->Address1: Main Street 24
Column3-->Address2: (stays empty)


If anyone has any idea, please let me know.

Thanks

[June7]

Address1: IIf(InStr([Address],"c/o")>0,Left([Address],InStr([Address],"c/o")-1),[Address])

Address2: IIf(InStr([Address],"c/o")>0,Mid([Address],InStr([Address],"c/o")+4),Null)

[Susy]

Hi June7,

thanks a lot.