We were working with a client in Melbourne recently, and a question came up (yet again) about how to manage some text that had been imported into Excel from an external database. This is becoming more common, as the number of people increases who use Excel to manipulate data originating from outside databases. Why’s it necessary? It’s because database designers might be primarily concerned with capturing data, rather than thinking ahead to what the user will need to use the data for. Have a look at the example below, in column B: [caption id="attachment_3164" align="alignnone" width="300"]
The text (column B) as it came into our Excel sheet from an external database[/caption] As you can see, the data appears in Last Name, First Name format, with both names being separated by a comma. If we use this for a mail merge in Word, for example, it’s going to be difficult to customise our field so our mailout is addressed to either Dear John, or Dear Mr. Symons. Here’s how we can fix it. 1) Insert 6 blank columns. I’ve added headers to show you what’ll go where. You’ll remove 4 of these plus one other when were finished. It’ll look like the example below: [caption id="attachment_3165" align="alignnone" width="1024"]
Temporary working columns C through H added in[/caption] 2) In Column C we locate the position of the comma that separate the first and last name. The formula we’ll put in cell C2 is: =(FIND(",",B2,1)-1 This means: “find the position number of the comma in cell B2 starting at character position 1, then subtract 1 from that number to ignore the position number of the comma.” This is the result: [caption id="attachment_3166" align="alignnone" width="394"]
Image 3 - Locating the comma's position[/caption] 3) In Column D we can now isolate the last name using the following formula: =LEFT(B2,C2) which we’ll enter into cell D2 This means “from the character string in cell B2, show the 5 characters starting from the left hand side”. This is the result: [caption id="attachment_3167" align="alignnone" width="507"]
Isolating the last name[/caption] 4) The next thing we need to do, in order to isolate the first name, is to determine how long the character string is. In Column E we use the following formula in cell E2: =LEN(B2) This means “what is the length of the character string in cell B2?” It looks like this: [caption id="attachment_3168" align="alignnone" width="634"]
Finding the string length[/caption] 5) Now that we know the total character length of B2 we can isolate the first name using the following formula in cell F2: =RIGHT(B2,(E2-C2-1)) This means “From the right hand side of cell B2 show the 5 characters, calculated by subtracting the comma position from the total length of the character string, then removing 1 more character. It looks like this: [caption id="attachment_3169" align="alignnone" width="749"]
Isolating the first name[/caption] At this point we’ve isolated the first name and the second name. We’ll now copy the contents of copy Column F to Column G and Column D to Column H. We’ll use Paste Values here, because we don’t want to copy the formulas, just the resulting values. 6) Select the range F2:F7. Press Control-C to copy the content, click into cell G2, then press Control-V to paste. At first, G2:G7 will display #VALUE! error because it has copied the formula and this is generating an error, as shown below: [caption id="attachment_3181" align="alignnone" width="227"]
Error after copy and paste[/caption] 7) Select the drop-down arrow on the smart tag (circled above) and select the Paste Values item (circled below) [caption id="attachment_3182" align="alignnone" width="273"]
Using Paste Values to fix the error[/caption] The first names will now appear in column G. While they look the same as the contents of F2:F5, they’re different as F2:F5 contain formulas whereas G2:G5 contains only text. [caption id="attachment_3171" align="alignnone" width="269"]
Paste Values completed correctly[/caption] 8) Repeat this process, copying the list of last names from cells D2:D7 to H2:H7, remembering to Paste Values This is what you’ll have: [caption id="attachment_3173" align="alignnone" width="1024"]
Paste Values completed for first and last name[/caption] 9) At this point, C through F, which were really only temporary work columns, aren’t needed any more. Also, Column B won’t be needed either. Delete columns B through The result is this: [caption id="attachment_3174" align="alignnone" width="560"]
The final result[/caption] And it’s done! If you need help with any Excel Issues you might have, let us know at enquiries@exceldimensions.com.au or call us on 1300 730 922. Can we help with your Excel on-site training requirements? Call us for information and pricing. We have a large client base Australia wide, and we’d be pleased to show you how we can increase your team’s Excel productivity. Note: we try and break down our examples into easy-to-follow steps. This may not be the most efficient way to solve the problem, but we think it’s easier to understand. And once you understand the parts of the solution, you can then put the solution together in a way that'll work for you.
The electronic spreadsheet has been in existence since 1978, but it was 1982 that Microsoft became involved. The first major leap occurred in 1978 when VisiCalc was created by Dan Bricklin, a student at Harvard Business School. It was basic software, capable of producing a spreadsheet of only 5 columns by 20 rows.
Among Excel users there’s a degree of uncertainty about the difference between Office 365 and Office 2013 . It’s a relatively new area, and while most of us are clear about what Excel 2013 is, it’s Office 365 that’s causing the confusion. Some consider that Office 365 is just another name for Excel 2013 (which isn’t the case)...
An on-site Excel course provides a great opportunity for participants to jump ahead in their knowledge and understanding of the most popular electronic spreadsheet program in the world. When training on-site, consider . the difference between a standard software course and one which will really boost a participant’s ability to use Excel effectively.
