Manipulating a string

Hi guys,

I have the below text in cell B2..

- This is the first point - This is the second point - This is the third point

Is there a formula I can use in cell A1 to insert text before the second and third dashes (not the first one). Basically i need to upload this csv to a website and cell B2 needs to read:

- This is the first point </br>- This is the second point </br>- This is the third point. So as with the html tags it displays

- This is the first point
- This is the second point
- This is the third point

Many Thanks,
Dean
deanlee17Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mvidasCommented:
I assume there is no space at the beginning of the string, what about simply finding " - " (without quotes) and replacing with " <br />- " ?

We could write a macro to look for hyphens not at the beginning of the string too, but if there is always a space on either side of your hyphens then you could use it to your advantage.

Matt
0
deanlee17Author Commented:
Yes we could indeed look for " - " there will not be a space at the start of the string. Could we do this in a formula so others could use it and simply copy it down the rows?
0
barry houdiniCommented:
You could do what Matt suggests with a SUBSTITUTE function, too, i.e. this formula

=SUBSTITUTE(B2," - "," </br>- ")

....or if you can't guarantee that there won't be a space at the beginning SUBSTITUTE can be set to explicitly replace the nth instance only so for 2 and 3

=SUBSTITUTE(SUBSTITUTE(B2,"-","</br>-",3),"-","</br>-",2)

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alexander Eßer [Alex140181]Software DeveloperCommented:
You also may try:
=CONCATENATE(MID(A1;1;FIND("-";A1;2) - 1);"<br>";MID(A1;FIND("-";A1;2);FIND("-";A1;2));"<br>";MID(A1;FIND("-";A1;FIND("-";A1;2)+1);LENGTH(A1)))

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.