Today’s Excel tip shows you how to extract information from a text file. In today’s example we have a list of street names with numbers and we want to sort by street name. There are several ways to accomplish this but this tip will introduce two new functions.
The first function is the MID function.
The function syntax has the following arguments:
MID(text, start_num, num_chars)
Text refers to the location of the cell where the text string is.
- Start_num is the position count you want to start extracting characters from the string.
- Num_chars tells the function how many characters to extract.
If all the addresses had consistent numbers in front of the street name (for example 4) we could just use =MID(A3,6,10). You always want to add 1 to the last character.
1234 Street — =MID(“1234 Street”, 6,10) would return “Street”
Since the addresses numbers are of different lengths we will also have to use the second function, the FIND function. The FIND function locates the first occurrence of a character or number and returns the relative position number. We will use it to find the FIRST SPACE between the number and the street name.
FIND(find_text, within_text, [start_num])
The FIND function syntax has the following arguments:
- Find_text Required. The text you want to find.
- Within_text Required. The text containing the text you want to find.
- Start_num Optional. Specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.
The formula will look like this:
Of course to use this formula you will need to insert a new column. To insert a new column right-click on the column letter and select “INSERT” from the list. Then enter the formula above (adjusting for location) and copy it to the bottom of your data.