| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Seperating text if there's more than a one space between them
Hi all I need to seperate text in a column into different columns. Text to columns doesn't work because it seperates all the text. In this column, I need the text that has more than three spaces between it to be moved. Eg. A1= Flat 1 Sky Way Scotland SW1 A2= 23 Dock Road Docklands DK3 A3= 161 Speed Drive Gasville GV7 Between the first set of text and the second, there is a minimum of 3 spaces. I desperately need the second lot in the second column. The Postcode is two spaces after the town's name which must not be affected, but rather move with the town's name. Please help if you can..!! Many thanks Joey -- Joey ------------------------------------------------------------------------ Joey's Profile: http://www.excelforum.com/member.php...fo&userid=8272 View this thread: http://www.excelforum.com/showthread...hreadid=502110 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Seperating text if there's more than a one space between them
Hi Joey,
I'd find and replace all spaces by three spaces before using Text to Columns -- Kind regards, Niek Otten "Joey" wrote in message ... Hi all I need to seperate text in a column into different columns. Text to columns doesn't work because it seperates all the text. In this column, I need the text that has more than three spaces between it to be moved. Eg. A1= Flat 1 Sky Way Scotland SW1 A2= 23 Dock Road Docklands DK3 A3= 161 Speed Drive Gasville GV7 Between the first set of text and the second, there is a minimum of 3 spaces. I desperately need the second lot in the second column. The Postcode is two spaces after the town's name which must not be affected, but rather move with the town's name. Please help if you can..!! Many thanks Joey -- Joey ------------------------------------------------------------------------ Joey's Profile: http://www.excelforum.com/member.php...fo&userid=8272 View this thread: http://www.excelforum.com/showthread...hreadid=502110 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Seperating text if there's more than a one space between them
You can *still* use TTC.
When you choose 'delimited' in the first window of the Wizard, there's an option in the second window: "Treat Consecutive Delimiters as One" Which, when *Checked*, should work perfectly for you. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Joey" wrote in message ... Hi all I need to seperate text in a column into different columns. Text to columns doesn't work because it seperates all the text. In this column, I need the text that has more than three spaces between it to be moved. Eg. A1= Flat 1 Sky Way Scotland SW1 A2= 23 Dock Road Docklands DK3 A3= 161 Speed Drive Gasville GV7 Between the first set of text and the second, there is a minimum of 3 spaces. I desperately need the second lot in the second column. The Postcode is two spaces after the town's name which must not be affected, but rather move with the town's name. Please help if you can..!! Many thanks Joey -- Joey ------------------------------------------------------------------------ Joey's Profile: http://www.excelforum.com/member.php...fo&userid=8272 View this thread: http://www.excelforum.com/showthread...hreadid=502110 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Seperating text if there's more than a one space between them
I would use Word's features for this.
Copy the cells to Word. Convert Table to Text. Go to Edit, Replace and, with Wildcards enabled, replace [ ^s}{3,} with ^t. Convert Text to Table (number of columns 2, separate text at tabs). Copy the cells to Excel. "Joey" wrote: Hi all I need to seperate text in a column into different columns. Text to columns doesn't work because it seperates all the text. In this column, I need the text that has more than three spaces between it to be moved. Eg. A1= Flat 1 Sky Way Scotland SW1 A2= 23 Dock Road Docklands DK3 A3= 161 Speed Drive Gasville GV7 Between the first set of text and the second, there is a minimum of 3 spaces. I desperately need the second lot in the second column. The Postcode is two spaces after the town's name which must not be affected, but rather move with the town's name. Please help if you can..!! Many thanks Joey -- Joey ------------------------------------------------------------------------ Joey's Profile: http://www.excelforum.com/member.php...fo&userid=8272 View this thread: http://www.excelforum.com/showthread...hreadid=502110 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
| Text Wrapping | Excel Discussion (Misc queries) | |||
| space between text strings with concatenate | Excel Discussion (Misc queries) | |||
| Autofitting a row | Excel Discussion (Misc queries) | |||
| Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Linear Mode
