Replace Carage return with vertical bar with vba

[neemi]

I have a excel speadsheet that I need to automate the import of it into an access table. However the address field in the spreadsheet is in one field as abbose to seperate fields for address lines 1 to 5. Also it is in the field with a carrage return (i.e. each address line on a new line in the field)

so for example the address is :

15 street name
area
town

in the one field.

I want to convert this to each line being in a seperate field.

I know I can propably do this using the Text to columns under the data menu, manually, if I can replace the carrage returns with say the vertical bar "|".

But all this needs to be automated through access.

Can anyone help.

Help and advice will be greatly appreciated as this is urgently required.

Cheers,

neemi

 

[combo]

If you can finish the work in access - you can create an UDF function with split, something like:

[tt]Function SplitAddress(S As String, I As Integer)
Dim V As Variant
V = Split(S, Chr(10))
If I > UBound(V) Then
SplitAddress = ""
Else
SplitAddress = V(I)
End Function[/tt]

and use SplitAddress([ImportedField],0), SplitAddress([ImportedField],1) etc. to create output table.

combo