Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Separate out data (2003)

    I've attached a sample spreadsheet. I need to pull out the different office name in this spreadsheet to a separate column. The offices are WAN, PEC, MC, QC. Unfortunately, the location of the office in each record is not consistent. Sometimes it's at the beginning, sometimes it's at the end, and once in a while it's even in the middle. This is a monthly data dump from our inventory program, and we can't control how the data is dumped. So, this is something we will need to do each month.

    Thank you!
    Attached Files Attached Files

  2. #2
    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: Separate out data (2003)

    How about the ARRAY function (conform with ctrl-shift-enter):
    =INDEX({"WAN","PEC","MC","QC","PRD"},MAX(IF(ISNUMB ER(FIND({"WAN","PEC","MC","QC","PRD"},A1)),{1,2,3, 4,5})))

    I added "PRD" since I presumed that "PRD" was also an office name from the line: "TYTON SEAL, PRD". You can add more if desired in the 2 arrays and increase the number of values in the 3rd array.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Separate out data (2003)

    Or, you can try this NON-ARRAY formula

    B1 :

    =LOOKUP(1,- FIND({"WAN","PEC","MC","QC","PRD"},A1),{"WAN","PEC ","MC","QC","PRD"})

    Regards
    Bosco

Posting Permissions

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