How to change formatting of text in a cell

[techie2013]

I am working with Excel 2010.

Each cell in my spread sheet column has some requirements text and a 'Note:' for that requirement. Sometimes there are two 'Notes' in one cell.

The text in each cell looks like this:

The user should be able to see all fields mentioned in 'access request functions list'.Note: See the document access request functions list' referenced above. Note: See list of fields in Appendix B.

All I need to do is find "Note:" and insert a line break before it. After applying that rule the cell text should look like this:

The user should be able to see all fields mentioned in 'access request functions list'.
Note: See the document access request functions list' referenced above.
Note: See list of fields in Appendix B.

Alternatively, I am happy to make just the "Note:" bolded. Like this:

The user should be able to see all fields mentioned in 'access request functions list'.Note: See the document access request functions list' referenced above. Note: See list of fields in Appendix B.


Any suggestions would be appreciated.

Thanks

 

[kevatarvind]

try below

Sub Test()
ActiveCell.Value = Application.WorksheetFunction.Substitute(ActiveCell.Value, "Note:", Chr(10) & "Note:")
ActiveCell.WrapText = True
End Sub

 

[techie2013]

Thank you for the solution. It worked like a charm when I selected one sell and ran the Macro. How can I run it on all the cell in a column. Thanks

 

[kevatarvind]

how many columns you have and in which column you want to run this code ?

 

[techie2013]

All my requirements are in one column. There are about 600 rows.

 

[kevatarvind]

select your Range where you want to run this code and run below code

Sub Test()
Dim cell As Range
For Each cell In Selection
    cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "Note:", Chr(10) & "Note:")
    cell.WrapText = True
Next
End Sub

 

[Rick Rothstein]

select your Range where you want to run this code and run below code

Sub Test()
Dim cell As Range
For Each cell In Selection
    cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "Note:", Chr(10) & "Note:")
    cell.WrapText = True
Next
End Sub

A little shorter (and faster)...

Sub PutNoteOnSeparateRow()
  Selection.Replace "Note:", vbLf & "Note:", xlPart
End Sub

 

[kevatarvind]

Very Nice Rick Sir its really amazing

 

[techie2013]

Yes!!! That worked for me. Thank you so much!
Can I ask you one more quick question since it is related. There is another column where I have list of items. When I run an alphabetical sort it gives me irregular results. I noticed that this is because many items have a 'leading' space (' '). How can I remove the leading spaces in all cells at one time?

 

[Rick Rothstein]

Yes!!! That worked for me. Thank you so much!
Can I ask you one more quick question since it is related. There is another column where I have list of items. When I run an alphabetical sort it gives me irregular results. I noticed that this is because many items have a 'leading' space (' '). How can I remove the leading spaces in all cells at one time?

Select the cells and run this one-liner macro against it...

Sub RemoveLeadingSpaces()
  Selection = Evaluate(Replace("IF(LEN(@),MID(@,FIND(LEFT(TRIM(@)),@),LEN(@)),"""")", "@", Selection.Address))
End Sub