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!


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

1. Use this formula to extract the first three characters:
and this one to extract the last three:

2. See my webpage for a VLOOKUP example.

Hope this helps you out.

BarrieBarrie Davidson