MSExcel: Data String/Cell Text Trick

This article will provide the Excel user with A) 2 methods to Separating text strings, B) Merging Separated Texts and C) Errors with separating texts.

Part A: Method 1 via Formula

Sometime when it comes to formulas, its much easier to build them as separate equations instead of building a single fancy one.

The first step is to build 1 simple and basic formula to do one basic task.  Subsequently, the next formula should also be simple and basic.  This secondary formula will either 1) use the results from the 1st formula or 2) create a different result from the 1st formula. The next formula will again either 1a) build upon the prior formula or 2a) add  the two previous formulas.

So as you can see by the above (hopefully), by keeping it simple, you can conserve time and energy and patience, instead of spending lots of time, trial and error creating a fancy one at the begin with.

Example 1: The following can be used for either separating names and or address’s:

Original Cell Text in B2.

The end result in F2.

The formulas for Row2 are provided in Row5:


Note 1: After ascertaining the desired results, you can replace the formulas with values, via copy and paste values option.

Note 2: If you want to show off you formula or preserve space, you can merge all the little formulas above into one fancy one.

Note 3: To extract the street/block number you can create an additional formula similar to the one with the “=RIGHT” fx above, except use the “LEFT” fx.  ( I wont provide with the details because if you got this far with your excel expertise, then i am sure you will easily apply the =Right fx without problems. But here’s a clue:  =LEFT(B3,C3) )

Method 2 Excel 2007 way

Excel 2007 and above also has a feature/wizard that will automatically convert text to columns. The button for it is under the File Menu > Data > Data Tools

Its great for separating 2 words into 2 columns, like names. But may not be 100% perfect for address’s because address’s include abbreviations, like NW., E., St., Dr, Apt (space) number, etc…  However, the wizard is indeed a time saving feature.

Tip1: Temporarily create a copy of the column and place it to the far right of your table before using the Text2Column wizard.  After the wizard you will see why it will need working room via blank / unused columns.  And if you dont provide working columns for the wizard, then it will mess up your table.

Tip2: You can use both Method 1 and Method 2 on same table.  Just be sure to use a copy of the table, instead of the original, when applying equations and the wizards. This way, the original table will be preserved while you test and experiment with formulas on the copy.

Part B – Merging Text

After text is separated into their individual columns, you re-merge some of the text.  This is useful for Address’s because you can leave the column with the street number alone but merge the street names back into a single string.

The formula for this is =Concatenate.  I wont tell you how to use, because the Excel Wizard will guide you easily.

btw: MSAccess can also separate and merge text as well. But many database people love to use excel in conjunction with Access because excel provides quick and easy ways to work with data.  Then the tables/spreadsheets can be copied back into Access or imported into Access (or linked by Access).

Part C – Errors with separating text strings

The issue is due to the “space” we see with our eyes, not being recognized by the machine, per se.  As a result, the formulas and wizards that rely on “space” being part of the text string don’t work.  I see this issue frequently when converting pdf’s into excel sheets and also when exporting access tables into excel sheets.

Fix 1: You can manually re-type the faulty text string into a new cell, ensuring you use the space bar in between the words and or numbers.  Then try your formulas or the wizard on this one cell and you will see the desired outcome.

Fix 2: Go down the list of text strings and remove that faulty space via delete or back space, then enter the valid space via your space bar.

Fix 3: you can use the =Substitute command to replace that invalid what-ever-it-is with a valid “space”.   Here is an example of formula, whereas the text is in A1 and the formula itself is in B1.

=SUBSTITUTE(A1,” “,” “)

As you can see in the above, it appears that i am substituting a space with a space in the text in Cell A1.  However, the first set of quotations marks actually has a copy of “what-ever-it-is” that is inside the text string of A1.  I bold-ed the area between the words, click copy, then pasted what-ever-is-in-the-clipboard, right in the first set of quotation marks inside the formula.

Of course the results in the formula will look exactly like the original text. The only difference is that the result via the formula now included a valid/recognizable “space” that can be used by formulas and wizards.

So the next step is to replace the equation and formulated results with plain text via copy and paste>special>values.