Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

2 Part: Splitting cell contents + using VLookup Formula

Posted by FD_Scott on September 28, 2001 5:28 AM
#1: SPLITTING A CELL: I have a column of Canadian Postal codes (alpha numeric). They are entered as three characters, a space, then three characters (i.e. L5J 3M9). I want to split this column into two columns where one would have the L5J, and the other would have the 3M9. How do I do this?

#2: VLOOKUP: Once I have split the cells, I would like to use a formula in a neighbouring column to look up the first three characters (L5J for instance) in another chart and automatically assign a value. For instance if L5J is assigned to John, then John's name will appear in the cell beside L5J. What formula do I use, and how should I setup the chart?


THANKS in advance!

Scott


Check out our Excel Resources

Re: 2 Part: Splitting cell contents + using VLookup Formula

Posted by Barrie Davidson on September 28, 2001 5:34 AM

1. Use this formula to extract the first three characters:
=MID(A1,1,3)
and this one to extract the last three:
=RIGHT(A1,3)

2. See my webpage for a VLOOKUP example.
http://ca.geocities.com/b_davidso/Web_Page_Files/Excel/lookup.html

Hope this helps you out.

Regards,
BarrieBarrie Davidson


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.