Extracting Data from a Text String

MID-FindToday’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:

=MID(A4,FIND(” “,A4,1)+1,20)

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.

 

 

Advertisements

About sandyluedke

Sandy has been a successful realtor in North Texas for over 25 years. She was consistently a top 100 producer in Texas, with Remax for many years. Sandy founded Ideal Real Estate Group as a way to not only continue to do what she does best...selling and listing homes...but as a broker to share her vast knowledge by mentoring new or struggling agents.
This entry was posted in Business, Ideal Real Estate Group, Interests, Microsoft Excel, Networking, Professional Business Networking. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s