MID: pull out text when you know the position

MID takes a starting position and a length. That makes it predictable for consistent record formats—and fragile when fields have variable widths.

Characters are numbered from 1: I is 1 and the first hyphen is 4, so the region begins at 5. The last example retains leading zeros because MID returns text. A shorter final segment does not need padding just to fit a requested extraction length.

Use A2 = INV-EAST-0042 and A3 = INV-WEST-019. The department field is four characters under this particular format.

Extract the middle segment of a structured ID

Formula syntax

=MID(text, start_num, num_chars)

What goes into it

A quick example first

=MID("CellLingo",5,5)

=MID(A2,3,4)

Before you copy this into a real sheet

Fixed positions are appropriate only for a documented fixed layout. If the prefix changes from INV to ORDER, the same positions return the wrong characters without necessarily producing an error. Check the delimiter locations and total length, or calculate positions with FIND for variable-width fields. Do not treat a non-error result as proof that parsing succeeded. For operational imports, preserve the original ID next to the extracted columns so an unexpected format can be traced.

Useful habits

• start_num is one-based, so the first character is position 1.
• Use SEARCH/FIND when start position depends on a delimiter.
• For delimiter-based extraction, TEXTBEFORE/TEXTAFTER can be easier to read.

Where people get caught out

• Treating the starting position as zero-based.
• Calculating a negative character count from missing delimiter positions.

Other functions worth a look

A detail worth remembering

When the starting position shifts from row to row, find the delimiter first or use a text-splitting approach. Changing the length alone will not fix the wrong start.