Spreadsheet Text Handling

Applications

Office Productivity

Spreadsheets

Metadata

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

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

=TEXTAFTER(A1,">")

Excel only

test string

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

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

Excel only

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

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

https://www.wallstreetmojo.com/change-case-in-excel/https://www.javatpoint.com/lower-in-excelhttps://www.javatpoint.com/uppercase-shortcut-in-excel (VBA AllCaps Macro)https://excel.officetuts.net/examples/change-lowercase-to-uppercase/https://www.educba.com/vba-ucase/https://www.educba.com/vba-lcasehttps://www.educba.com/vba-strconv/https://support.microsoft.com/en-us/office/customize-the-quick-access-toolbar-43fff1c9-ebc4-4963-bdbd-c2b6b0739e52
https://www.excel-easy.com/examples/substring.htmlhttps://www.ablebits.com/office-addins-blog/excel-substring-functions-extract-text/https://www.excelforum.com/excel-formulas-and-functions/1065793-fqdn-to-short-name-if-applicable.html
https://chandoo.org/forum/threads/count-number-of-periods-in-a-cell.41030/https://www.ablebits.com/office-addins-blog/remove-first-last-characters-excelhttps://superuser.com/questions/483419/how-to-split-a-string-based-on-in-ms-excel
CONCATENATEhttps://support.google.com/docs/answer/3094123?hl=en-GB
TEXTSPLIT / SPLIThttps://www.reddit.com/r/sheets/comments/106zthp/what_is_googlesheets_equivalent_of_excels/https://stackoverflow.com/questions/62979743/how-to-get-the-nth-text-after-a-delimiter-in-excel