How To Use Mid Function In Excel

Description

The Excel MID function returns a certain number of characters from the center of the specified text cord.

As a quick example, =MID("worksheets",5,6) returns "sheets."

In obviously English language, y'all tell Excel to pick a text string (the first argument; "worksheets") and extract a certain number of characters from information technology by specifying the location of the first character from where to first the extraction (the second argument; "five" or the first letter of the alphabet "s" in the give-and-take "worksheets") and the number of characters to desire to extract (the third statement; "6" or "sheets").

Purpose

The function helps extract the characters from a string which would be problematic to pull using the Excel LEFT function or RIGHT function.

When combined with other functions similar SEARCH and LEN, y'all tin piece and dice any data with ease, be information technology names, dates, or CSV files.

Syntax

=MID(text,start_num,num_chars)

Arguments

text – the location of the value you desire to excerpt from.
start_num – the position where the selection starts.
num_chars – the number of characters that you want to excerpt from the cord.

Return Value

The MID part returns the extracted characters as string/text values.

Usage Notes

1. Applying the MID function to numeric values converts them into text/cord values.

Using the MID function to convert numbers to text

two. If the num_chars argument is greater than the overall number of characters in the text string, Excel returns all of the remaining characters in the string starting from the location specified past the start_num argument.

three. If start_num is less than "1," the #VALUE! error is triggered.

4. When the start_num argument is greater than the overall number of characters in your text string, the MID function returns an empty string.

When start_num is greater than the overall number of characters

v. If num_chars equals goose egg("0"), the MID office returns an empty string.

six. Each space is counted as ane graphic symbol.

Examples

For you to amend understand how the formula works, let's choice one phrase "James Bond 007" and run with it to show you different ways to use the Excel MID function.

This MID function returns 4 characters starting at the seventh graphic symbol:

          =MID("James Bail 007",vii,4) // Output: Bail

This MID function returns 3 characters starting at the twelfth character:

          =MID("James Bond 007",12,four) // Output: 007

This function extracts everything starting at the seventh character since the num_charts statement is greater than the remaining number of characters in our string.

          =MID("James Bond 007",7,100) // Output: Bond 007

How to Utilise the MID Function in Excel VBA

Idea we were done? Well, not so fast.

The Excel MID function can also be used in VBA, providing you with more flexibility with your data. Here's a quick example showing you how to utilize the MID part in Excel VBA.

          Sub VBA_MID_Function() MidValue = Mid(Range("A2"), seven, iv) Range("B2") = MidValue Cease Sub

If you're unfamiliar with VBA, here's how yous tin can accommodate.

First, leave the first and last lines of lawmaking unchanged. These are merely containers holding the VBA lawmaking together.

The 2d line of the VBA code declares a new variable (MidValue) storing the extracted characters using the MID part.

This VBA MID function selects cell A2 and extracts 4 characters starting at the seventh graphic symbol, returning the discussion "Bail."The third line is responsible for moving those extracted characters from the VBA editor into cell B2.