text from one column into multiple columns

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

Guest

Hi,
How can I take text entered like this:
Mike Smith Toyota
123 Main St
Anytown, TX 12345
phone 713 222 1212
fax 713 222 2121

....and move it to where every address element (name, street, city, etc.) is
in its own column? Text to Columns obviously won't work, and I've had no
luck trying to make a macro to do it.
Thanks,
Jeff
 
Jeff

This can be parsed with code but normally with these questions we are not
getting all the information.

Do all the entries have five lines?
Is there a gap between each group?
If they do not all have five lines are there gaps in the groups?
Is the same data always in the same line of a group (City, State, Etc)?

Answers to these may help someone to look at it

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Hi!

Copy>Paste Special>Transpose

Then, if needed, use T to C to further separate "Anytown,
TX 12345"

Biff
 
Hi
then for a non-VBA solution. enter the following in A1 on your second sheet
8assuming your sourece data starts in A1 on a different sheet called
'data'):
=OFFSET('data'!$A$1,(ROW()-1)*5+COLUMN()-1,0)
and copy this 5 cells to the right and down.
After this copy this range and insert it with 'Edit - Paste Special -
Values' to remove the formulas
 
Back
Top