Add carriage return where comma exists 3

[andyb2005]

Hi

I have a list of addresses in an address column in an excel document, that has commas separating the full address.

What I would like to be able to do is to have a piece of vba code go through the column and remove the commas, adding a carriage return.

Does anyone have a piece of code as an example?

 

[SkipVought]

hi,

You could do it without code
1. new column (assuming that the address is in col A...
[tt]
=LEFT(A1,FIND(",",A1)-1)&CHAR(10)&RIGHT(A1,LEN(A1)-FIND(",",A1))
[/tt]


Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[TonyJollans]

Hi Andy,

I just recorded changing a comma to a full stop and then changed the recorded code to make it use a line feed instead and this is what I got

[blue]Sub Macro4()
'
' Macro4 Macro
' Macro recorded 18/05/2005 by Tony
'

    Cells.Replace What:=",", Replacement:=vbLf, LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub[/blue][

It works on the whole sheet; I'm sure you can change it to work on your column.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[TonyJollans]

Just saw Skip's post - why not just

[blue][tt] =SUBSTITUTE(A1,",",CHAR(10))[/tt][/blue]

(and don't forget to format the target cell to Wrap Text)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[earthandfire]

If you need the full carriage return, you would need to change Skip's solution to ... & CHAR(13) & CHAR(10) ..., or Tony's from vbLF to vbCRLF

Hope this helps.

 

[andyb2005]

Hi

 =SUBSTITUTE(A1,",",CHAR(10))

The above works fine except that it creates a carriage return and includes a space at the new line before the data starts.

 

[TonyJollans]

Hi Andy,

I can't make it do that. Is it definitely putting a carriage return there, or is that an assumption based on what you see? Could you have text aligned at the bottom and a cell with more lines somewhere else on the same row perhaps?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[andyb2005]

Hi Tony

I can live with it ;o)

Many thanks

 

[SkipVought]

Tony,

Whay do I continue to FORGET SUBSTITUTE???

I just love simplicity, when at all possible!

Thanx! ==> *

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]