Strings

How many time a char appears in a string

A:\BCDE\FG\HIJ\KULWANT\my.pdf

• Search how many times “\” appears in this cell (A1)

=(LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””)))/LEN(“\”)

Find the position of character occurring nth time in a string

• Find the position of “\” when it is occuring 4th time in the string in cell (A1)

=FIND(CHAR(1),SUBSTITUTE(A1,”\”,CHAR(1),4))

Get Yahoo Finance data in Excel Sheet

File with VBA script

Macros

1. Script to Remove Images and convert table to plain text in word document

Sub MakePlainText()
Call DeleteAllShapes
Call RemovePictures
Call DeleteAllShapes
Call TablesDeleteAll
End Sub

Sub TablesDeleteAll()
    Dim tbl As table
    For Each tbl In ActiveDocument.Tables
        tbl.ConvertToText (wdSeparateByParagraphs)
    Next tbl
End Sub
Sub DeleteAllShapes()
Dim shp As Word.Shape
For Each shp In ActiveDocument.Shapes
    shp.Delete
Next
End Sub
Sub RemovePictures()
Dim objPic As InlineShape
For Each objPic In ActiveDocument.InlineShapes
objPic.Delete
Next objPic
End Sub

2. Give option to select a folder. Go through all files in the folder and chop them and do amendments in doc as per our requirement. Then use the two chopped one and create a final one. This make no sense to chop and combine them again but as we use to sell 2 chopped products and only recently start selling the third product, so it make sense to use the existing code and create a final document.
Make sure Microsoft scripting runtime reference is selected.

VBA script

3. Increase the fonts size:

Sub Font_change_14point()
'
' Font_change_14point Macro
' Changes document into 14 point font document
'
    With ActiveDocument
        .UpdateStylesOnOpen = True
        .AttachedTemplate = _
            "Z:\Ostendo\LargeFont\Templates\Large Font 14 Template.dotx"
        .XMLSchemaReferences.AutomaticValidation = True
        .XMLSchemaReferences.AllowSaveAsXMLWithoutValidation = False
        .PageSetup.RightMargin = 18
    End With
End Sub
Sub Font_change_11point()
'
' Changes document into 11 point font document
'
    With ActiveDocument
        .UpdateStylesOnOpen = True
        .AttachedTemplate = _
            "Z:\Ostendo\LargeFont\Templates\Large Font 11 Template.dotx"
        .XMLSchemaReferences.AutomaticValidation = True
        .XMLSchemaReferences.AllowSaveAsXMLWithoutValidation = False
        .PageSetup.RightMargin = 18
    End With
End Sub
Sub Font_change_16point()
'
' Changes document into 16 point font document
'
    With ActiveDocument
        .UpdateStylesOnOpen = True
        .AttachedTemplate = _
            "Z:\Ostendo\LargeFont\Templates\Large Font 16 Template.dotx"
        .XMLSchemaReferences.AutomaticValidation = True
        .XMLSchemaReferences.AllowSaveAsXMLWithoutValidation = False
        .PageSetup.RightMargin = 18
    End With
End Sub

4. CU3

CU