Using Replace() on multiple occasions in a string

[davedave24]

I have a set of addresses that are 3-5 lines, that go into various worksheet cells that are often 2, 3 or 5 lines. The cells they go into are merged cells.

How would I use Replace() to replace the vbNewLine with ", " but only on certain occurrences?

For example the address is stored in this format (in a single cell):

Address Line 1
Address line 2
Address line 3
Address line 4
Address line 5

These need to go into a merged cell like this:

Address line 1, address line 2
Address line 3, address line 4, address line 5

Or sometimes with 3 lines, like this:

address line 1
address line 2, address line 3
address line 4, address line 5


I can use:

 replace(textaddress, vbnewline, ", ", 1, 1)

But how do we do this for multiple occurences?

Would it be easier to just paste the formula into the cells and use the excel function Substitute()? If so then I have that already working as thus:

(for 3 line address)

=SUBSTITUTE(SUBSTITUTE([Note.xlsm]Details!$D$4,CHAR(10),", ",3),CHAR(10),", ",3)

for 2 line address

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Note.xlsm]Details!$D$4,CHAR(10),", ",1),CHAR(10),", ",2),CHAR(10),", ",2)

 

[SkipVought]

Hi,

"Sometimes"?

How does that convert to logic?

BTW, I'd use the Split() function. But then you STILL need to figure out some logic for "sometimes."

You might also take a look at using the Data > Text to columns feature.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Sub SomeTimes()
Dim StrIn As String, StrOut As String, i As Long, j As Long
StrIn = "Address line 1,address line 2,Address line 3,address line 4,address line 5"
Randomize Timer
StrOut = Split(StrIn, ",")(0)
For i = 1 To UBound(Split(StrIn, ","))
  j = Int(Rnd * 2)
  If j = 1 Then
    StrOut = StrOut & "," & Split(StrIn, ",")(i)
  Else
    StrOut = StrOut & vbCr & Split(StrIn, ",")(i)
  End If
Next
MsgBox StrOut
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

Paul, what the hey -- just roll the dice! Great logic! ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}