# Thread: Need Help with 'OFFSET' function (2003 SP3)

1. ## 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. ## 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. ## 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

4. ## 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.

5. ## 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

6. ## 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.

7. ## 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
•