Multiple Find line break & replace and fomatting

paddydive

Active Member
Joined
Jun 30, 2010
Messages
460
Hi,
I have a excel sheet wherein i want to perform certain task based on the line break as below

1) i want to change font to bold from objective till Pre-requisite ... (this i have done)
2) After Pre-requisite if there is a period (dot) and line break then to remove period(dot) ..... this is i am struggling for


HTML:
Objective:

This data has to be in Bold


Pre-Requisites:
I) If there are a period and a line break the remove period.
II)If there are a period and a line break the remove period.
III)If there are a period and a line break the remove period.

Expected Result:

some text here

please help :(
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
bump....

Even this is not working... guys can you halp i am going mad with this

HTML:
  rng.Characters(Len(rng.Value), 1).Text = ""


:oops:
 
Upvote 0
Hi Paddy,

May i know the Input and the desired output? Your first post is not clear to me.
 
Upvote 0
Thanks Vds1,

I have a sheet where the cells contains something like this ;

In Cell L2

Objective:
asdfasdflasndfldfnadfadf asdfasdfsa

Pre-Condition:
1) conditions no 1.
2) conditions no 2.

Expected Result:
this is expected result.


So what i need as a output is
1) The macro should remove all the full stops (periods / dots) which are present at the end of a line, or in simple word remove all the dots which are followed by a line break.
2) Remove the full stops/periods from the end of the line.

To make it more simple i do not want any full stop/period in the cell

Hope i have explained it clearly.
 
Upvote 0
Please find my below code,
I am basically looping through all the characters in a cell and finding out the new line character, with the help of its ASCII value. Here I found that, ASCII value for new line is 10.
While looping the cell character if I match ASCII value 10, then I tried comparing its previous character is equal to “.”, if so then replace by blank.

Code:
Dim i As Long


For i = 0 To Range("L2").Characters.Count
    If Asc(Range("L2").Characters(i, 1).Text) = 10 Then
       If Range("L2").Characters(i - 1, 1).Text = "." Then Range("l2").Characters(i - 1, 1).Text = ""
    End If
Next

I think in your code, you may have to replace Range("L2") as "Rng"

If you simply want to delete the "." from the cell and replace it to "" then you can try this,

Code:
Range("L2").Replace ".","",XLPART



HTH
 
Last edited:
Upvote 0
Thanks vds1,

That worked for me, i wanted to loop through all the characters in a cell only.

One quick question, In case of multiple
new line characters and "." then
After each replacement of a "." with "" the count of characters in a cell reduces by one every time, which give error No - 5 in for loop.
If we give a "Exit for" then it will not loop through the rest of the characters. Any solutions for this.
 
Upvote 0
Got the answer, i am looping it in reverse way like, not sure is it technically the right way.

Do let me know if you have any other way to do this.

HTML:
 For i = Range("L2").Characters.Count To 0 Step -1
     If Asc(Range("L2").Characters(i, 1).Text) = 10 Then
          If Range("L2").Characters(i - 1, 1).Text = "." Then 
              Range("l2").Characters(i - 1, 1).Text = ""
              i =  Range("L2").Characters.Count
          End If
    End If
Next
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top