Search / Replace with NewLine

**drew138** · 03-11-2009, 07:12 PM

Hi there, I've been using the forum for weeks and it's been an awesome resource. Having fun leaning and applying here but I've stumbled onto an issue I can't resolve.

I've got a text field that looks like this:

++ Information in bullet point 1 ++ Information in bullet point 2 ++ Information in bullet point 3

I need a code sample that will give me an output that results in a bullet list using Char(149) to replace "++". I've tried a few things but I can't figure out how to NOT get the macro to put a 'newline' in front of the first bullet.

The desired output would be this (where * = Char(149)):
--
* Information in bullet point 1
* Information in bullet point 2
* Information in bullet point 3
---
But I keep getting:
----
[extra line space]
* Information in bullet point 1
* Information in bullet point 2
* Information in bullet point 3
---
Can anyone help with this one!

Thanks.

Drew

**shg** · 03-11-2009, 07:44 PM

=mid(substitute(a1, "++", char(10) & char(149)), 2, 1024)

**drew138** · 03-11-2009, 08:32 PM

I see. That makes sense but I'm not sure how to program that in Macro form? I'm still a noob. I'm using

PHP Code:


    Cells.Replace "++", vbLf

And this gives me a line break where I want it; but I can't seem to get the both the newline and the Char(149).

Maybe on on the wrong track? The data is in column F is that is helpful?

Thanks.

**shg** · 03-11-2009, 08:34 PM

Hold on, Drew. You posted the question in the programming forum, I answered you there, then you deleted that post and posted a new thread in the General forum, I answer here, now you are back to wanting a macro?

**drew138** · 03-11-2009, 08:42 PM

Apologies. I originally posted in the "Excel Worksheet Functions" sub-forum and deleted it as I was looking for a Macro from the get-go since I'm stringing this routine together with several other features. My humble and sincere apologies for the confusion!