Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    The Land of Pleasant Living, Maryland, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with simple formula (Excel 2003)

    I've been away from EXCEL for some time and can't remember a simple text merge formula. Here's the crux of the problem:
    - I have a 4000 row spreadsheet
    - One column (call it A) contains a 5-digit zip code formatted as text
    - Another column (call it contains LastName data (all caps)

    The problem:
    I want to create a KEY field (call it C) which contains the 5 digit zip followed by a "dash" followed by the first six characters of the LastName.

    Examples:

    ZIPCode .......... LastName .......... Desired KEY
    12345 .......... WASHINGTON .......12345-WASHIN
    01998 .......... JEFFERSON .......... 01998-JEFFER

    That's it - I know it's a simple CONCATENATION or STRING function but I can't seen to get it to work.

    Thanks in advance to any lounger who can get me back on track.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with simple formula (Excel 2003)

    Hi try this

    =A2&"-"&(MID(B2,1,6))
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help with simple formula (Excel 2003)

    If the zipcodes are numbers (and not text) so are formatted to appear with leading zeroes, you may need:
    =Text(A2, "00000")&"-"&(MID(B2,1,6))

    Steve

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    The Land of Pleasant Living, Maryland, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with simple formula (Excel 2003)

    Steve,

    Thanks for your help - worked like a charm.

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    The Land of Pleasant Living, Maryland, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with simple formula (Excel 2003)

    FY,

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •