What can you do when someone gives you a list of first and last names typed into a single column in Excel and you need to split them into two columns? How can we grab just part of a special code or phone number within a column? When I teach Excel, I have students toss me these questions looking for any possible solution and so here I’ll provide a little help.
There are two primary solutions to splitting up or extracting text: Text to Columns and use of formulas. The easiest way to go is to use Text to Columns; however, this requires that your data uses consistent characters like commas, spaces or dashes. Likewise, I’ve found that Text to Columns should not be used in some cases where the original list of information is likely to change again and again. Formulas should be used when you anticipate the original information will update because they will change along with that update.
What I wanted to concentrate on in this article was using formulas to split text because it’s a far more challenging (and exciting) subject.
Scenario #1: Consistent Characters
The fundamental formulas to know when splitting text are LEFT, RIGHT and MID:
| Function | What it does |
|---|---|
| =LEFT(cell, # of characters) | Extracts the indicated number of characters at the beginning of the code. |
| =RIGHT(cell, # of characters) | Extracts the indicated number of characters at the end of the code. |
| =MID(cell, starting character #, # of characters) | Extracts the indicated number of characters from the middle of the code, starting with the character you specify. |
With this example, you can see that when given an exact and consistent number of characters, the formulas are quite easy to use. To obtain the area code, I used the LEFT function, asking for three characters. For the last four digits, I used the RIGHT function, asking for four characters. For the middle three numbers, I used the MID function, asking it to start with and include the fifth character and return three total characters.
Scenario #2: Variable Characters with No Symbols Used
In this scenario, I’m given a list of codes and told that I need to extract just the text part out of it. Unfortunately, there is no formula in Excel like, “=JUSTTHETEXT (cell)”. That would be nice.
What will help us to solve any problem is finding a consistency or pattern. Note that while all of the codes may have a variable number of letters, they all have the exact same number of digits that follow. If this is the case with your list, here’s a great function to know:
| Function | What it does |
|---|---|
| =LEN(cell) | Counts the total number of characters typed in a cell |
You cannot express to Excel, “I want just the letters”. However, you can say, “Give me all of the characters except for the last four”.
With this example you can see that instead of typing an exact number into LEFT function, I used LEN to count the total number of characters. LEN will return a 6 from A1. If I subtract 4 from 6, I get 2, which is the correct number of letters I need. LEN will return a 7 from A2. If I subtract 4 from 7, I get 3, which is the correct number of letters again.
Scenario #3: Variable Characters with Symbols
Note that in this scenario, there is a dash used in each of the codes. However, the letters and the numbers vary from code to code. If I am required to obtain both the text and the numbers, the resulting formulas will be quite different from one another.
In order to solve this problem, we need a function that can discover the location of the dash:
| Function | What it does |
|---|---|
| =FIND(“character”, cell) | Returns the number where the specified character is located in the code |
Observe from the example that when I use FIND to discover the location of the dash, it returns 3 from A1 and 4 from A2. If I were to use that number in a LEFT function, it would not only return the letters, but it would also include the dash.
When I revise the formula subtracting 1 from the result, it now returns the correct number to use in our LEFT function.
The complete solution to obtaining the characters will appear like this. The FIND function returns the number where the dash is located. We subtract 1 from that to indicate “all of the characters before the dash”. When placed into the LEFT function, it returns the letters to the left of the dash.
Because the FIND function counts characters from left to right, returning the numbers after the dash will be a slightly bigger challenge. For this solution we will need both FIND and LEN.
If we subtract the number of characters up to and including the dash from the total number of characters in the cell, we can return the characters found after the dash. For A1, the LEN function returns a 6. The FIND function discovers the dash as character 3 in A1. If we subtract 3 from 6, we get 3, which is the number of digits that follow the dash. In A2, the LEN function returns a 7. The FIND function discovers the dash at position 4. If we subtract 4 from 7, we get 3 which is the number of digits after the dash.
The solution provided shows us using the RIGHT function because we need the characters at the end of the code. We subtract the result of FIND (all of the characters up to and including the dash) from the result of LEN (all of the characters in the cell), returning the number of characters found after the dash. When that number is used in the RIGHT function, we return the digits after the dash.
- Jeff Bongiovani
Let’s look at the big picture first and then try to break down the individual parts. SQL Server can be the data storage mechanism for a variety of needs. Some organizational data may be from other platforms such as Oracle, DB2, Excel, Access, or even XML documents to name a few. SQL Server Integration Services (SSIS) automates the process of warehousing a variety of corporate data. SSIS then performs ETL (extract, transform, and load) operations to product data marts. These data marts contain specialized databases that can be used to simplify reporting. Both Microsoft SharePoint Server (SPS) and SQL Server Reporting Services (SSRS) can consume data from these specialized data marts. Clients then use and analyze data from SPS or SSRS or even access the data marts directly using a client application like Excel or Crystal Reports.
SQL Server uses Integration Services (SSIS) to provide the Extract-Transform-Load (ETL) operations for Data Warehousing. This draws data from a variety of data sources such as existing OLTP databases running SQL Server, Oracle, DB2, or any other ODBC data source. SSIS can also collect data from smaller sources such as locally hosted Access databases, Excel spreadsheets, XML documents, and other local database platforms. During the extraction process, SSIS validates and combines data, removes duplicate data, organizes data for export to applications, and even edits the data to make cross-platform data consistent. Once warehoused, the data can be made available to users or exported to Data Marts. Data marts are specialized databases that organize data for reporting. These databases may focus data and make it easier for clients to build reports. The warehouse may also supply data for SQL Server Analysis Services (SSAS) to create data cubes that roll-up and aggregate data. SSAS also supports very advanced Data Mining algorithms to provide complex data analysis. SSIS sequences all these operations from connecting to the data sources, to the warehousing, to the exporting to data marts, to the processing of cubes and data mining models.
In addition to providing storage and ETL operations, SQL Server also includes Reporting Services (SSRS) to provide front-end presentation services to clients. SSRS contains a report designer hosted in the Business Intelligence Development Studio (BIDS). This tool allows report developers to create rich, dynamic reports that can be rendered into a variety of formats such as web-base reports, PDF documents, Excel spreadsheets, or Word Documents. In addition to a report designer, SSRS incorporates a database to store and secure reports. It also provides a web based interface so clients can open and run reports ad-hoc or on a schedule. This interface can be hosed independently, or it can be incorporated into a Windows SharePoint Services solution to maintain a more familiar user environment.
The clients are the ultimate consumers of data. Their need for data needs to be flexible. Data can be provided from many different data marts that are specialized for whatever applications the user needs. These clients may be using web-based reports or spreadsheets from SSRS. They may be designing their own reports using Crystal Reports and directly accessing the data marts. They may be viewing reports hosted in Windows SharePoint Services. These SharePoint reports can be custom built data services or a SSRS report. The clients consume this variety of reports from a variety of platforms. Desktops, laptop, tablets, and smartphones all have applications that can consume data.
Microsoft SharePoint 2010 makes it easier for people to work together. Using SharePoint 2010, your people can set up Web sites to share information with others, manage documents from start to finish, and publish reports to help everyone make better decisions.
