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 :(
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

paddydive

Active Member
Joined
Jun 30, 2010
Messages
460
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:
 

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,194
Hi Paddy,

May i know the Input and the desired output? Your first post is not clear to me.
 

paddydive

Active Member
Joined
Jun 30, 2010
Messages
460
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.
 

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,194

ADVERTISEMENT

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:

paddydive

Active Member
Joined
Jun 30, 2010
Messages
460
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.
 

paddydive

Active Member
Joined
Jun 30, 2010
Messages
460
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

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
Top