| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
text from one column into multiple columns
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 |
|
#2
|
|||
|
|||
|
Hi
do you always have the same amount of rows per record? -- Regards Frank Kabel Frankfurt, Germany Jeff Brown wrote: 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 |
|
#3
|
|||
|
|||
|
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 HIS "Jeff Brown" <Jeff wrote in message ... 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 |
|
#4
|
|||
|
|||
|
yes, they all have 5 rows per record
thanks, Jeff "Frank Kabel" wrote: Hi do you always have the same amount of rows per record? -- Regards Frank Kabel Frankfurt, Germany Jeff Brown wrote: 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 |
|
#5
|
|||
|
|||
|
Hi!
CopyPaste SpecialTranspose Then, if needed, use T to C to further separate "Anytown, TX 12345" Biff -----Original Message----- 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 . |
|
#6
|
|||
|
|||
|
thanks!
"Biff" wrote: Hi! CopyPaste SpecialTranspose Then, if needed, use T to C to further separate "Anytown, TX 12345" Biff -----Original Message----- 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 . |
|
#7
|
|||
|
|||
|
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 -- Regards Frank Kabel Frankfurt, Germany Jeff Brown wrote: yes, they all have 5 rows per record thanks, Jeff "Frank Kabel" wrote: Hi do you always have the same amount of rows per record? -- Regards Frank Kabel Frankfurt, Germany Jeff Brown wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Putting text in a column based on variable text from another colum | Excel Discussion (Misc queries) | |||
| how do I easily convert a single column of text (multiple rows si. | Excel Discussion (Misc queries) | |||
| Is there a way I can enter a letter to proceed text a column of ce | Excel Discussion (Misc queries) | |||
| How can I see column headings of hidden columns in Excel before u. | Excel Discussion (Misc queries) | |||
| splitting text to multiple columns | Excel Discussion (Misc queries) | |||
Linear Mode
