Carriage Returns in Excel Fields

[guyKn]

I've imported text fields from a filemaker address database which uses 'carriage returns' to seperate each address line.

Anyone know how to either remove the carriage returns and relace them with commas or better still import each address line into seperate excel fields.

Any help much appreciated

GK

 

[dyarwood]

This may not work or be a very long way round but gud you import the data into a word document then use the Text to Table function under the Table tab on the menu bar (after highlighting it) then with the table created select it all and copy and paste into a spreadsheet. Not sure if this would be of any help. Let me know

dyarwood

 

[xlbo]

Import it and select the column, then run this:

Sub SplitData()
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=Chr(10), FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End Sub

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[guyKn]

hi,

Thanks for this, I've done this but was trying to find a way of automating it within Excel rather than copying and pasting the data backwards and forwards from excel and word.

Thanks any way

GK