Apply function to selected cells.

**dymention2** · 05-13-2012, 10:36 AM

I have a custom function called Sentence(). It will capitalize the first letter of the first word of every sentence in a cell and turn everything else into lower case.

Currently I have to make a separate sheet and link to the cell I want to apply it to. However, Id like to make a macro that will apply this function to a selection of cells. This should be fairly simple but I haven't had much luck making it work by my self.

**TMS** · 05-13-2012, 10:45 AM

It might generate more help if you share the function and a sample workbook.

Regards, TMS

**dilipandey** · 05-13-2012, 10:56 AM

Hi dymention2,

Select the range where you wish to apply the formula (use ctrl key to select if the range is breaking / separate from each other). and press ctrl + Enter.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**dymention2** · 05-13-2012, 11:26 AM

It might generate more help if you share the function and a sample workbook.

Regards, TMS

Here is the example spreadsheet: Sentence Function Example.xlsm

Here is the function:

Function Sentence(ByVal txt As String) As String
Dim m As Object
txt = LCase(txt)
txt = Application.Replace(txt, 1, 1, UCase(Left$(txt, 1)))
With CreateObject("VBScript.RegExp")
.Pattern = "\.\s."
.Global = True
For Each m In .Execute(txt)
txt = Application.Replace(txt, m.FirstIndex + 1, m.Length, UCase(m.Value))
Next
End With
Sentence = txt
End Function

**TMS** · 05-13-2012, 12:46 PM

Thanks for the sample and code ... please note that the forum rules state that you need to use code tags around your code snippets.

I'll try and have a look in the meantime but cannot respond until you add the tags.

Regards, TMS