Ravindra BagaleCourses & study guides

5. Data Cleaning A–Z

5.13 Extracting Parts of Text

By Last updated:

Before

Order Note
Order BLK-260314-001 delivered in 9 mins
Order AMN-260314-006 delivered in 10 mins
Order BLK-260314-005 delivered in 18 mins

After

Order ID Mins
BLK-260314-001 9
AMN-260314-006 10
BLK-260314-005 18

Steps in Excel

  1. Order ID (always 14 characters, starting after "Order "): =MID(A2,7,14).
  2. Minutes between "in " and " mins": =VALUE(MID(A2,FIND("in ",A2)+3,FIND(" mins",A2)-FIND("in ",A2)-3)).
  3. Microsoft 365 / Excel 2024: =TEXTBEFORE(TEXTAFTER(A2,"Order ")," ") and =--TEXTBEFORE(TEXTAFTER(A2,"in ")," mins").
  4. Or Flash Fill with two examples.

Ravindra Bagale's Tip

Khup students position (7, 14) hard-code kartat aani ekhada note vegla lihila ki answer chukta. Fixed length khatri asel tarach hard-code kara; nahitar FIND kiwa TEXTBEFORE/TEXTAFTER ne "markers" shodha. Extract kelela number VALUE ne number banva – nahitar average hot nahi.

Practice task

From notes like "Rider Raja delivered BLK-260314-007 at 19:51", extract the rider name, the order ID and the time (as a real time value).