E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

Change Your Life with Excel Getting Clever with Words

elcome to Change Your Life with Excel, bringing

you another couple of doses of Excel trickery to

help you save time and finish work earlier.

In this edition we look at some techniques to split (or

parse) cell values into 2 or more separate cells. This can

be useful if for example you have each full staff name in

a single cell but need to split them into first and last

names. One of these techniques will introduce you to a

couple of text functions which can be combined to do

some very clever parsing.

As always, the features covered here are based on Excel

2003 however most can still be found in other versions.

Tip Using Text to Columns

Some of you may unwittingly be familiar with the Text to

Columns feature if you’ve ever opened a text file in Excel.

A series of Text Import dialog boxes appears which is

identical to the Text to Columns dialog boxes.

To illustrate how Text to Columns works let’s assume

that we have a column of dates which need separating

into day, month and year.

• Select the column containing the dates

• From the Menu Bar select Data | Text to

Columns

A Text to Columns dialog box appears which first asks

whether our data is Delimited or Fixed Width.

Delimited means that there is a common character or

symbol which separates the items we want to split.

Fixed Width means that we could draw a vertical line

down the column to separate the items we want to split.

• Select Delimited since the items we want to split

are not perfectly vertically aligned

• Click Next

Determine what character or symbol splits each item to

be separated (this could be a space or a tab for

example).

Here we can see that a “/” splits the days, months and

years. Since this is not a predefined option we need to

tell Excel that this is our Delimiter.

• Tick Other and enter a “/” in the adjacent box

Notice in the Data preview pane how the days, months

and years are now neatly split

• Click Next

By default Excel will overwrite the original column of

complete dates. If we want to preserve this column we

may want to split the days, months and years into

columns B, C and D. To do this:

• Enter cell reference B1 into the Destination field

• Click Finish

We now have our dates split into separate cells as

required. Note how the “/” character has disappeared

since this was used as the Delimiter.

W

By Leigh Drake Director Arc Business Processes www.arcbusiness.com.au

E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

Note that you may need to reformat the numbers in

column A.

We can also use this feature to easily split text. For

example the names below can be split into first and last

names by ticking the Space option as a Delimiter in the

2nd step of the Text to Columns feature.

Trick Extract Words from a String of Text

Suppose we have a list of names or other text and we

want to separate out, say, every first and last word. Doing

this manually on anything but a short list would be very

time consuming. Instead we can combine 2 functions,

SUBSTITUTE and LEN to do this in seconds.

The example we will use shows various product items

which comprise a product code, description and colour

which we want to split into 3 columns.

Since each description can comprise a different number

of words which we need to keep together, we can’t

simply use the Text to Columns feature above.

This is a 5-step process. Steps 1 to 4 will insert a unique

character immediately after the first word and

immediately before the last word. Step 5 will then use

Text to Columns to split the text using the unique

character as a Delimiter.

Step 1

• In cell B2 type the function

=SUBSTITUTE(A2,“ ”,“*”,1)

This function says:

- reference the text in cell A2, and

- replace each space (denoted by “ ”, a space

enclosed by 2 double quotes),

- with an asterisk (denoted by “*”), but

- only replace the first instance of a space

(denoted by the 1)

The asterisk is our unique character which will be our

Delimiter.

This converts the text

C45901 Copy Paper A3 Blue

to

C45901*Copy Paper A3 Blue

Step 2

Unfortunately there’s no easy way of telling Excel to now

find the last instance of a space so we have to apply a bit

of logic.

To help us do this we need to get rid of all remaining

spaces.

• In cell C2 type the function

= SUBSTITUTE(B2,“ ”,“”)

E-TIPS

Finance, Valuation, Risk & Modelling

your one-partner solution for building skills and knowledge

This says:

- reference the text in cell B2, and

- replace each space (denoted by “ ”, a space

enclosed by 2 double quotes),

- with nothing (denoted by “”, 2 double quotes)

By leaving the instance number blank, all remaining

spaces will be replaced.

This converts the text

C45901*Copy Paper A3 Blue

to

C45901*CopyPaperA3Blue

Step 3

• In cell D2 type the formula

=LEN(B2)-LEN(C2)

LEN counts the number of characters in a string of text

(including spaces). Therefore this formula subtracts the

number of characters in the text in cell C2 (22) from the

number of characters in the text in cell B2 (25). This tells

us the number of spaces remaining in the text in cell B2

(3)

Step 4

We can now insert an asterisk immediately before the

last word.

• In cell E2 type the function

=SUBSTITUTE(B2,“ ”,“*”,D2)

D2 in this case is 3 so this function replaces the 3rd

instance of a space with an asterisk which results in the

text:

C45901*Copy Paper A3*Blue

• Copy the formulas in cells B2 to E2 to the end of

the list

• Highlight column E and select Edit | Copy

• With column E still highlighted select Edit |

Paste Special and select Values

• Click OK

This removes the formulas in column E

Step 5

• Highlight column E

• Use Text to Columns as above and use * as the

Delimiter

3 columns will be created containing the product code,

description and colour. Columns A to D can now be

deleted and we can add appropriate column titles. Phew!

Once you get the hang of this trick you’ll see how flexible

it can be and what could have taken hours now just takes

seconds. Don’t you just love Excel?

Until next time, happy Excelling.