Spreadsheet Text Handling

TODO

Metadata

For the examples in this section, cell A1 contains...

This is my <M> test string

LEN

=LEN(A1)

26

Substring

For the examples in this section, cell A1 contains...

This is my <M> test string

MID

=MID(A1,6,2)

is

LEFT

=LEFT(A1,4)

This

RIGHT

=RIGHT(A1,4)

ring

FIND

=FIND("is",A1) 

3

=LEFT(A1,FIND("<",A1)-1)

=TEXTBEFORE(A1,"<")

Excel only

This is my 

This is my 

=RIGHT(A1,LEN(A1)-FIND(">",A1))

=TEXTAFTER(A1,">")

Excel only

 test string

 test string

=MID(A1,FIND("<",A1)+1,FIND(">",A1)-FIND("<",A1)-1)

=TEXTBEFORE(TEXTAFTER(A1,"<"),">")

Excel only

M

M

TEXTSPLIT / SPLIT

To extract a number from a SQL server build string. In this case we want to extract the 4175 from 16.0.4175.1 in cell B16...

=NUMBERVALUE(INDEX(TEXTSPLIT(B16,"."),3))

Excel only

=VALUE(INDEX(SPLIT(B16,"."),3))

Google only

4175


4175


String Manipulation

For the examples in this section, cell A1 contains...

ExAmple tExt

TRIM

REPT

SUBSTITUTE

CONCATENATE

Change Case

For the examples in this section, cell A1 contains...

ExAmple tExt

UPPER

=UPPER(A1)

EXAMPLE TEXT

(EXCEL) Convert to UPPERCASE without using a formula...

Perhaps you just want to cut & paste information from various sources and convert it all to uppercase as a one-off task....

You can create a VBA (Visual Basic for Applications) shortcut. Use Left Alt + F11  to start VBA (or View - Macros - View Macros - Create)... Select the sheet and Insert - Module (cut & paste the script shown below). 

Note that once created your sheet will need to be saved in .xlsm (Excel with Macros) format.

Sub AllCaps()  

    For Each Cell In Selection  

        If Not Cell.HasFormula Then  

            Cell.Value = UCase(Cell.Value)  

        End If  

    Next Cell  

End Sub 

To add the Macro to the Quick Access Toolbar..
  • select the dropdown at the right of the toolbar then M (More commands).
  • Choose commands from: Macros.
  • Choose the AllCaps macro and Add>>
  • M (Modify...) to chosse a custom icon

To create a shortcut key...  View - Macros - View Macros - Options...

LOWER

=LOWER(A1)

example text

(EXCEL) Convert to lowercase without using a formula...

Perhaps you just want to cut & paste information from various sources and convert it all to uppercase as a one-off task....

You can create a VBA (Visual Basic for Applications) shortcut. Use Left Alt + F11  to start VBA (or View - Macros - View Macros - Create)... Select the sheet and Insert - Module (cut & paste the script shown below). 

Note that once created your sheet will need to be saved in .xlsm (Excel with Macros) format.

Sub NoCaps()  

    For Each Cell In Selection  

        If Not Cell.HasFormula Then  

            Cell.Value = LCase(Cell.Value)  

        End If  

    Next Cell  

End Sub 

To add the Macro to the Quick Access Toolbar..
  • select the dropdown at the right of the toolbar then M (More commands).
  • Choose commands from: Macros.
  • Choose the NoCaps macro and Add>>
  • M (Modify...) to chosse a custom icon

To create a shortcut key...  View - Macros - View Macros - Options...

PROPER

=PROPER(A1)

Example Text

(EXCEL) Convert to Initial Capitals without using a formula...

Perhaps you just want to cut & paste information from various sources and convert it all to uppercase as a one-off task....

You can create a VBA (Visual Basic for Applications) shortcut. Use Left Alt + F11  to start VBA (or View - Macros - View Macros - Create)... Select the sheet and Insert - Module (cut & paste the script shown below). 

Note that once created your sheet will need to be saved in .xlsm (Excel with Macros) format.

Sub InitCaps()  

    For Each Cell In Selection  

        If Not Cell.HasFormula Then  

            Cell.Value = StrConv(Cell.Value, vbProperCase)  

        End If  

    Next Cell  

End Sub 

To add the Macro to the Quick Access Toolbar..
  • select the dropdown at the right of the toolbar then M (More commands).
  • Choose commands from: Macros.
  • Choose the NoCaps macro and Add>>
  • M (Modify...) to chosse a custom icon

To create a shortcut key...  View - Macros - View Macros - Options...

Bibliography