software

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: www.excelforum.com/member.php...foamp;userid=8272
View this thread: www.excelforum.com/showthread...hreadid=502110Hi Joey,

I'd find and replace all spaces by three spaces before using Text to Columns

--
Kind regards,

Niek Ottenquot;Joeyquot; gt; wrote in message
...
gt;
gt; Hi all
gt;
gt; I need to seperate text in a column into different columns.
gt; Text to columns doesn't work because it seperates all the text.
gt;
gt; In this column, I need the text that has more than three spaces between
gt; it to be moved.
gt;
gt; Eg.
gt; A1= Flat 1 Sky Way Scotland SW1
gt; A2= 23 Dock Road Docklands DK3
gt; A3= 161 Speed Drive Gasville GV7
gt;
gt; Between the first set of text and the second, there is a minimum of 3
gt; spaces. I desperately need the second lot in the second column.
gt; The Postcode is two spaces after the town's name which must not be
gt; affected, but rather move with the town's name.
gt;
gt; Please help if you can..!!
gt;
gt;
gt; Many thanks
gt; Joey
gt;
gt;
gt; --
gt; Joey
gt; ------------------------------------------------------------------------
gt; Joey's Profile:
gt; www.excelforum.com/member.php...foamp;userid=8272
gt; View this thread: www.excelforum.com/showthread...hreadid=502110
gt;
You can *still* use TTC.

When you choose 'delimited' in the first window of the Wizard, there's an
option in the second window:
quot;Treat Consecutive Delimiters as Onequot;
Which, when *Checked*, should work perfectly for you.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
quot;Joeyquot; gt; wrote in message
...
gt;
gt; Hi all
gt;
gt; I need to seperate text in a column into different columns.
gt; Text to columns doesn't work because it seperates all the text.
gt;
gt; In this column, I need the text that has more than three spaces between
gt; it to be moved.
gt;
gt; Eg.
gt; A1= Flat 1 Sky Way Scotland SW1
gt; A2= 23 Dock Road Docklands DK3
gt; A3= 161 Speed Drive Gasville GV7
gt;
gt; Between the first set of text and the second, there is a minimum of 3
gt; spaces. I desperately need the second lot in the second column.
gt; The Postcode is two spaces after the town's name which must not be
gt; affected, but rather move with the town's name.
gt;
gt; Please help if you can..!!
gt;
gt;
gt; Many thanks
gt; Joey
gt;
gt;
gt; --
gt; Joey
gt; ------------------------------------------------------------------------
gt; Joey's Profile:
gt; www.excelforum.com/member.php...foamp;userid=8272
gt; View this thread: www.excelforum.com/showthread...hreadid=502110
gt;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.

quot;Joeyquot; wrote:

gt;
gt; Hi all
gt;
gt; I need to seperate text in a column into different columns.
gt; Text to columns doesn't work because it seperates all the text.
gt;
gt; In this column, I need the text that has more than three spaces between
gt; it to be moved.
gt;
gt; Eg.
gt; A1= Flat 1 Sky Way Scotland SW1
gt; A2= 23 Dock Road Docklands DK3
gt; A3= 161 Speed Drive Gasville GV7
gt;
gt; Between the first set of text and the second, there is a minimum of 3
gt; spaces. I desperately need the second lot in the second column.
gt; The Postcode is two spaces after the town's name which must not be
gt; affected, but rather move with the town's name.
gt;
gt; Please help if you can..!!
gt;
gt;
gt; Many thanks
gt; Joey
gt;
gt;
gt; --
gt; Joey
gt; ------------------------------------------------------------------------
gt; Joey's Profile: www.excelforum.com/member.php...foamp;userid=8272
gt; View this thread: www.excelforum.com/showthread...hreadid=502110
gt;
gt;