Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Edgecomb, Maine
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Help with 'OFFSET' function (2003 SP3)

    I have a spreadsheet with names and addresses in column A. I need to sort it out so that each name and address is in the same row with the data in different columns, sort of like this:

    Joe Smith
    123 Anystreet
    Yourtown, AY 12345
    111-222-3333

    to
    Joe Smith 123 Anystreet Yourtown, AY 12345 111-222-3333

    After that I can parse it down to separate city/town/state

    I have done both before, using OFFSET to move from columns to rows, but can't seem to get it to work today.

    Ideas on simple formula to separate the city/town/state would also be helpful.

    Thanks,

    --Jim

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help with 'OFFSET' function (2003 SP3)

    We'd need to know more about the exact layout of the worksheet. A sample workbook would be handy.

    With a text such as Yourtown, AY 12345 in cell A1, the following formulas will return the town, state and zip code:

    =TRIM(LEFT(A1,FIND(",",A1)-1))

    =TRIM(MID(A1,FIND(",",A1)+1,FIND(" ",A1,FIND(",",A1)+2)-FIND(",",A1)))

    =TRIM(MID(A1,FIND(" ",A1,FIND(",",A1)+2)+1,100))

  3. #3
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Edgecomb, Maine
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help with 'OFFSET' function (2003 SP3)

    Hi Hans,

    Thanks for your help. I did get one of my old formulas to work, not sure why it wouldn't earlier. I have included it in this sample workbook, it has 2 sheets. It would be easiest if I could do all the offsetting and parsing with one set of formulas, but it may be too complex. Please let me know what you think.

    --Jim
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help with 'OFFSET' function (2003 SP3)

    Don't have time to work it all out, but look at the bold cells on the offset sheet. Perhaps you can use those as a starting point.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Edgecomb, Maine
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help with 'OFFSET' function (2003 SP3)

    Hans,

    Thank-you for your time and effort. Please look at the Offset sheet. I have the formulae across the top, in the first two rows. When I select the first row, and copy down, everything gets offset by 2 cells to the right, take a look. What do you think? Am I trying to do too much in one pass. Maybe I need to keep it simple to get it from Column A of sheet 1 to Row one of the Offset sheet, then parse.

    --Jim
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help with 'OFFSET' function (2003 SP3)

    The main point is that each set of data consists of 4 rows, so you must multiply the row number with 4 instead of 3 in the offset.
    And since you aren't just transposing the data any more but also pulling them apart, it doesn't make much sense to use the column number, you can't fill the formulas to the right anyway.
    See attached version.
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Edgecomb, Maine
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help with 'OFFSET' function (2003 SP3)

    Hi Hans,

    Thank-you so much! You nailed it! I know you're a pro, I do okay with functions until I get stumped. I don't have the knowledge or the patience to try to figure it out.

    Thanks once again,

    --Jim

Posting Permissions

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