MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


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