Extract sub-string of number from field of long series of numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have field with 9 digit zip codes (5 digit plus 4 digits). I want to
create two separate fields from this one field - one field with 5 digits and
the othe field with 4 digits.

Thanks
 
If the codes are of the form 12345-6789 say in A1, then

=left(A1,5) will return the first 5 and
=right(A1,4) will return the last 4
 
If the zip codes are text (which they probably are to be able to display the
leading zero), try this:

Select the zip code cells
Data>Text-to-Columns

-->if there is a dash in the zip code:
choose Delimited and use the dash as the delimiter

-->if there is no dash in the zip code:
choose Fixed Width and place the break point after the fifth character

Then click finish


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Actually, if you use Text-to-Columns....

after you select the break point or the delimiter, you need to click Next so
you can indicate that both columns are Text.

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Back
Top