Extracting data is a common task for many office workers. Everyone has their own tips. Today we introduce three methods, from the simplest shortcut techniques to tried-and-true classic formulas.
The data source is in column A and contains many items. We need to extract name, height and weight. You can see the items to extract follow a pattern: they are the data that follow the second and third commas in the source.
When we face a problem, finding the pattern is the key to solving it. Now that the pattern is identified, the solutions follow. Here are three methods, from simple keyboard shortcuts to powerful classic formulas; each is introduced below.
3 methods to quickly extract data from multiple rows
- Enter John in B2.
- Enter Mary in B3.
- Excel will automatically fill the data below.
- Press Enter to finish.
- Select the data → Data → Text to Columns.
- Delimiters
- Comma (use whatever delimiter applies to your data)
- Completed
Formula:
=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",99)),(COLUMN(A:A)-1)*99+1,99))
- SUBSTITUTE($B34,",",REPT(" ",99)) replaces the ',' with 99 spaces, resulting in "John………………………………………………………………………………………165cm………………………………………………………………………………………74kg" (spaces shown as '.' for clarity).
- (COLUMN(A33)-1)*99+1 is (1-1)*99+1=1. When dragged to the right, the 1st value is 1, the 2nd value is 99×1, the 3rd value is 99*3.
- MID(SUBSTITUTE($B34,",",REPT(" ",99)),(COLUMN(A33)-1)*99+1,99) extracts 99 characters starting from the 1st character, producing "John………………………………………………………………………………………165cm………………………………………………………………………………………74kg" (spaces shown as '.' for clarity).
- Finally, use TRIM to remove the extra spaces.
