Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Get nth match with INDEX / MATCH in Excel
  • How to get last column number in range in Excel
  • Extract data with helper column in Excel
  • How to use Excel TRANSPOSE Function
  • Complete List of Excel Lookup and Reference Functions, References and Examples

Data Analysis Examples

  • How to calculate current stock or inventory in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Subtotal function in Excel
  • How to add Trendline to a chart in Excel
  • How to create Gauge Chart in Excel

Data Validation Examples

  • Excel Data validation exists in list
  • How To Create Drop-down List in Excel
  • Excel Data validation must contain specific text
  • Excel Data validation must not contain
  • Excel Data validation allow uppercase 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.
Worked Example:   Split dimensions into three parts in Excel

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.
Worked Example:   How to extract multiple lines from a cell in Excel

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

Worked Example:   Convert feet and inches to inches in Excel

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:

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

Next Post:

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

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
  • Extract last name from full name — Manipulating NAMES in Excel
  • How to extract name from email address in Excel
  • Split numbers from units of measure in Excel
  • How to create email address with name and domain in Excel
  • UPPER function: Description, Usage, Syntax, Examples and Explanation
  • Next biweekly payday from date in Excel
  • EDATE function: Description, Usage, Syntax, Examples and Explanation
  • Steps to create Dynamic calendar grid in Excel
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • YEARFRAC function: Description, Usage, Syntax, Examples and Explanation
  • ODDLYIELD function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annuity for interest rate in excel
  • TBILLPRICE function: Description, Usage, Syntax, Examples and Explanation
  • ODDFPRICE function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYBS function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles