Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to Sort by Color in Excel
  • Randomize/ Shuffle List in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • Calculate Conditional Percentile ‘IF’ in table in Excel

References

  • Find closest match in Excel
  • How to retrieve first match between two ranges in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • Count rows with at least n matching values
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation require unique number
  • Excel Data validation must contain specific text
  • Excel Data validation date in specific year
  • Excel Data validation exists in list
  • Excel Data validation unique values only

MID, MIDB functions: Description, Usage, Syntax, Examples and Explanation

by

What are MID, MIDB functions in Excel?

MID, MIDB functions are  TEXT functions in Microsoft Excel.

MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

While, MIDB returns a specific number of characters from a text string, starting at the position you specify, based on the number of bytes you specify.

Important:

  • These functions may not be available in all languages.
  • MID is intended for use with languages that use the single-byte character set (SBCS), whereas MIDB is intended for use with languages that use the double-byte character set (DBCS). The default language setting on your computer affects the return value in the following way:
  • MID always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
  • MIDB counts each double-byte character as 2 when you have enabled the editing of a language that supports DBCS and then set it as the default language. Otherwise, MIDB counts each character as 1.

The languages that support DBCS include Japanese, Chinese (Simplified), Chinese (Traditional), and Korean.

Syntax of MID, MIDB functions

MID(text, start_num, num_chars)

MIDB(text, start_num, num_bytes)

The MID and MIDB function syntax has the following arguments:

  • Text: The text string containing the characters you want to extract.
  • Start_num: The position of the first character you want to extract in text. The first character in text has start_num 1, and so on.
  • Num_chars: Specifies the number of characters you want MID to return from text.
  • Num_bytes: Specifies the number of characters you want MIDB to return from text, in bytes.

MID, MIDB formulas explanation

  • If start_num is greater than the length of text, MID returns “” (empty text).
  • If start_num is less than the length of text, but start_num plus num_chars exceeds the length of text, MID returns the characters up to the end of text.
  • If start_num is less than 1, MID returns the #VALUE! error value.
  • If num_chars is negative, MID returns the #VALUE! error value.
  • If num_bytes is negative, MIDB returns the #VALUE! error value.

Example of MID, MIDB functions

Steps to follow:

1. Open a new Excel worksheet.

2. Copy data in the following table below and paste it in cell A1

Note: For formulas to show results, select them, press F2 key on your keyboard and then press Enter.

You can adjust the column widths to see all the data, if need be.

Data
Fluid Flow
Formula Description Result
=MID(A2,1,5) Returns 5 characters from the string in A2, starting at the 1st character. Fluid
=MID(A2,7,20) Returns 20 characters from the string in A2, starting at the 7th character. Because the number of characters to return (20) is greater than the length of the string (10), all characters, beginning with the 7th, are returned. No empty characters (spaces) are added to the end. Flow
=MID(A2,20,5) Because the starting point is greater than the length (10) of the string, empty text is returned.

Post navigation

Previous Post:

DCOUNT function: Description, Usage, Syntax, Examples and Explanation

Next Post:

231 Keyboard Shortcut Keys In Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Logical Functions, References and Examples
  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel TRUE Function
  • How to use Excel FALSE Function

Date Time

  • How to get number of days, weeks, months or years between two dates in Excel
  • WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation
  • Get days, months, and years between dates in Excel
  • NETWORKDAYS.INTL function: Description, Usage, Syntax, Examples and Explanation
  • Find Last Day of the Month in Excel

Grouping

  • How to randomly assign people to groups in Excel
  • Group numbers at uneven intervals in Excel
  • Group times into unequal buckets in Excel
  • Categorize text with keywords in Excel
  • Running count group by n size in Excel

General

  • Share Excel data with Word documents
  • Common Errors in Excel
  • How to count total number of cells in a rectangular range in Excel
  • How to get Excel workbook path only
  • Subtotal invoices by age in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning