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?action=getinfo&userid=8272
View this thread: http://www.excelforum.com/showthread.php?threadid=502110

0
1/17/2006 4:50:37 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
484 Views

Similar Articles

[PageSpeed] 33

Hi Joey,

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

-- 
Kind regards,

Niek Otten


"Joey" <Joey.21sg1m_1137516903.0147@excelforum-nospam.com> wrote in message 
news:Joey.21sg1m_1137516903.0147@excelforum-nospam.com...
>
> 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?action=getinfo&userid=8272
> View this thread: http://www.excelforum.com/showthread.php?threadid=502110
> 


0
nicolaus (2022)
1/17/2006 5:04:52 PM
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" <Joey.21sg1m_1137516903.0147@excelforum-nospam.com> wrote in message 
news:Joey.21sg1m_1137516903.0147@excelforum-nospam.com...
>
> 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?action=getinfo&userid=8272
> View this thread: http://www.excelforum.com/showthread.php?threadid=502110
> 

0
ragdyer1 (4060)
1/17/2006 7:28:27 PM
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?action=getinfo&userid=8272
> View this thread: http://www.excelforum.com/showthread.php?threadid=502110
> 
> 
0
MartinP (78)
1/17/2006 7:41:03 PM
Reply:

Similar Artilces:

Converting selected section of text to numbers
Could someone please help me to extract some details from "1/10/2004 through 31/01/2005", which has been pasted into one cell. I need a formula to extract the month, 10 and another formula to extract the number 01 I then need to convert those calendar months to fiscal units (or fiscal months), so 10 becomes 4 and 01 becomes 7. Please note that these dates change and may not always have the same number of characters. ie 1/10/2004 could be 11/10/2004, etc. I need to be able to then do a calculation on those 2 fiscal units (eg 7-4=3). I hope that's clear. Rob presume you...

rotate text
I know how to rotate a text box, but how do you nudge the rotation? Open the measurements toolbar. Use the angle adjustment. -- Ron "jc1063" <jc1063@discussions.microsoft.com> wrote in message news:5F488BF1-B52A-487D-BC90-E77EBB445D94@microsoft.com... >I know how to rotate a text box, but how do you nudge the rotation? ...

How do I switch text from capitals to normal?
If I type an email in all CAPS, is there a way to make it into regular form wthout re-typing whole email? Yes, but the method depends on the version of Outlook and the Editor used. chris verbeski wrote: > If I type an email in all CAPS, is there a way to make it into regular form > wthout re-typing whole email? For m, it works like this - select all the text, then hold down shift and tap the F3 key, that toggles the text between lower case, capitals and then capital first letter for every word. It doesnt' work everywhere but works in Word. "Bob I" ...

Text in cell #2
I am trying to find out how I can enter text in a cell and then press enter in the same cell to add more text. The keystroke is Alt+Enter to create a paragraph return. :) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Heather" <Heather @discussions.microsoft.com> wrote in message news:BFFAFA7F-4C45-42A6-96D6-5FB471C82EAA@microsoft.com... > I am trying to find out how I can enter text in a cell and then press enter > in the same cell to add more text. ...

Vertical Alignment within a Line of Text
I have a line of text that has text and pictures. I would like to have the picture vertically centered with the text, rather than being aligned at the bottom. I am using Microsoft Word for Mac. I have tried Format>Document>Layout>Vertical Alignment>Top/Apply To: Selected Text. Doing so does not provide the correct results. Any suggestions? ...

Reply Message displays columm of the words "Plain Text"
In Outlook 2003, when I reply to a message, the reply message goes into normal view with a columm of the words "Plain Text" on the left side of the reply message. How can turn off the columm of the words "Plain Text" in normal view when I reply to a message? "Word 2003 to edit email message and Word 2003" and "Word 2003 to read Text email message" is check on. it sounds like you have the option enabled to view styles in use. can you make the column wider and narrower by dragging the center line? I'm not sure how to disable it other than it&...

Help with seperating data?
Hello I was wondering if anyone knows a way of seperating chunks of data into specific lines. i.e: THIS: href="3353-12American Baby href="1721-6American Cheerleader href="10a8-12American City & County href="2928-24American Conservative href="1072-6American Cowboy href="6035-6American Handgunner href="3343-6American Heritage href="2498-4American Heritage of Invention & Technology BECOMES THIS: href="3353-12American Baby href="1721-6American Cheerleader href="10a8-12American City & County href="2928-24American Conse...

Format Text for all new visio diagrams
Could someone please explain how to change the basic font from aria to some other font for all text in new visio diagrams? I am sure this must seem simple, but I just have not ever been able to do this. -- William W. Taylor You can edit the Normal style under Format > Define Styles and change the font for a drawing there. Many styles are based on Normal. Normal is the default style for blank drawings. You might also try and find BLNKDG_U.VSD or BLNKDG_M.VSD in the Visio install directories. Perhaps you can change that file (in Visio 2003). It's an idea I haven't tried yet....

Displaying Text
Hello, I have an MFC application, where I'm interested in displaying some text for the user in the client area. To do this, I create a device context, and use the .TextOut method to display CStrings. The problem begins when the window is resized, or when a menu that hides the text pops - once the menu is closed, or after the window has been resized, the text (or parts of it) are being erased. How can this be solved? I read that when the window is resized, OnPaint is called, and thus I tried displaying the text from there - hoping to re-display it "after" it has been erased. The...

Linking body text to multilevel lists that update
When writing large documents with numbered multilevel lists, is it possible to link body text to the numbers so that they both change together? For example: (Text for section 3.5) 3.1.5 Refer to item 2.1. If the document gets revised, and section 2.1 becomes section 3.1, it automatically updates (numbering), but the text in what is now section 4.1.5 still refers to section 2.1, which is incorrect. Can these be linked so that when the number becomes 3.1 (in the numbered multilevel list), the body text will also update automatically? The same situation exists when referenci...

Restoring one Dynamics table instead of the whole database
Is it possible to restore just a table into the SQLdatabase instead of the entire database? We accidentally deleted a budget and would like to restore just the GL00200 and the GL00201 tables... thanks! -- Doug I restored the backup into a test company (KB 871973). I then used DTS to copy the data to the live company. -- Thanks Titan "Doug" wrote: > Is it possible to restore just a table into the SQLdatabase instead of the > entire database? We accidentally deleted a budget and would like to restore > just the GL00200 and the GL00201 tables... > > thanks...

Masking the sent from field with a seperate domain?
We have Exchange 2k3 and AD setup. We use the pop3 connector to download email from various seperately hosted domains and route the email to the correct client. Our users have noticed that any email they send shows the recipient the local domain in the sent from field. We need to mask it to say that it come from a different domain... for example one of the domains that we use to download email from using the pop3 connector. Is this possible? Any help would be greatly appreciated. Jackie Jackie wrote: > We have Exchange 2k3 and AD setup. We use the pop3 connector to > down...

Outlook 2003
Hi, I remmeber that there is a way that company address and conatcts should be filled to as many contacts (persons) that you have as soon as you fill company info. For example if John is working for Dell at address: xxx Then if another guy with the name James works for Dell, once I fill company name Dell it should fill his address and main phon number. But in my MS Outlook 2003 is not working? Any idea why? Is there any settings that I missed? Thanks, Are you using Save & New... Contact from company? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours...

Text to Columns...
Does anyone know the official function of the menu option called "Text to Columns..." which is found under the "data" option on the menu bar? How does it work? What is it supposed to do? Answers to these questions would be appreciated. Thank you... Craig craig_madrin@goodyear.com It allows you to separate text in a cell or column of cells either by position (fixed) or based on a delimiter, and place the separated data in one or more columns. It can also do some rudimentary data manipulation, including parsing dates, converting numbers to text, text, etc. Th...

Seperators
Hi I hope that someone can help? I have been sent a work book where several columns have data seperations between words as follows: "UK Pounds" The data appears to have a square box in between the "UK" and the "Pounds" Please can you advise a quick way to change the format in all columns in the worksheet Saved from a previous post: Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm Since you do see a box, then you can either fix it via a helper cell or a macro: =substitute(a...

Renaming menu item and changing its prompt text...?
Hi, I rename one menu item at runtime as follows... int nCount = pMenu->GetMenuItemCount(); for (int i = 0; i < nCount; i++) { CString str; pMenu->GetMenuString(i, str, MF_BYPOSITION); if (str.CompareNoCase(_T("Old Name")) == 0) { UINT nId = pMenu->GetMenuItemID(i); pMenu->ModifyMenu(nId, MF_BYCOMMAND, nId, _T("New Name")); break; } } How can I change also its prompt text? TIA, -HS "HS" <anonymous@discussions.microsoft.com> wrote in message news:0e7101c3b40d$2864b910$a301280a@phx.gbl... > Hi, > ...

Modified POP Report prints correctly on all but one workstation
5 user environment on GP 9.0. All users share same modified reports dictionary. Modified blank purchase order has lines between each item. The lines print correctly from all workstations except for one. On the workstation that will not print the lines between the items, it prints correctly to the screen (with lines shown) but not to the printer. Same printer shared between all users. The only thing I have not checked is logging in as another user on that workstation to see if the same error occurs. I'm at a loss of what to try! Please help! Thanks, Kristi Sampsel Kristi, Pl...

have 3 pieces of data in a cell seperated by a comma. How can I isolate the middle piece of data?
I've imported a large file of data that is basically three columns of data each seperated by a comma. (I could not figure out how to import them into a new spreadsheet so that each piece of data was automatically put into it's own column - but that is a different question) How can I do a "search replace" that will delete all of the data up to the firts comma? Then I would want to delete the data after the second comma in order to isolate the middle portion of data? Here is an example of the data: "10302003-12445","17.99","xyz21" Is there a be...

transferring address book from outlook express on one computer to.
i want to copy my address book from my main computer to my laptop. i was using outlook express and now am using outlook 2003. So how did you back up this address book and in what format is it now? Have you already transferred this data to Outlook Express? That would be the first step you would need to take. Once you do, you can import into Outlook. -- Russ Valentine [MVP-Outlook] "milpj4" <milpj4@discussions.microsoft.com> wrote in message news:F1DFDA41-1120-4701-9DBB-60754473C9E5@microsoft.com... >i want to copy my address book from my main computer to my laptop. i ...

pasting text into a text box
For some reason I am unable to paste text into a chart text box, (with excel 2007). Is there a trick to this? Gary K. Five minutes with Excel 2007 show that there is no obvious way to paste copied text into the embedded text box. Aren't you glad you upgraded? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Gklass" <gmklass@ilstu.edu> wrote in message news:1180231963.336906.112430@m36g2000hse.googlegroups.com... > For some reason I am unable to paste text into a chart ...

Default Text
How can I change the text default from "plain text" to "HTML"? I know it is probably easy, but I can't find the option anywhere. Any help is welcome. You don't say what version of Outlook you are using, but in 2003 (and XP, if I remember correctly), at the Inbox menu, select Tools, Options, Mail Format. Judy Freed Systems Development UNC Charlotte "Nancy" <photoconcepts@charter.net> wrote in message news:960801c4338e$4b5dfd40$a501280a@phx.gbl... > How can I change the text default from "plain text" > to "HTML"? I know i...

I Want to Caculate only ONE Cell !!!
Dear I Wannt to make the excel sheet only calculate one cell that I put a rule in it. Also, can I make the Excel refresh automatically every certain period of time to calculate that Cell.? Thank You Tools|Options|Calculate and check "Manual" You can either trigger the recalcualation manually (select the cell put your curser in the formula bar and press enter) Or by VBA where you could wrap Workbooks("Book1").Worksheets("Sheet1").[A1].Calculate insided a timing routine. Jerry Mahmoud Metwally wrote: > Dear > I Wannt to make the excel sheet onl...

text : Frequency
Hi, I need to know how many time I have a stringA StringB and a StringC in a column. I think frequency is only for numbers. How can I do it with Strings? So With the frequecies of each 3 strings,I will be able to build a flowchart. Thanks, J Jack use COUNTIF For example: =COUNTIF(A1:A200,B1) for a range in column A; adjust to suit or: =COUNTIF(A:A,B1) for all of column A Regards Trevor "Jack" <anonymous@discussions.microsoft.com> wrote in message news:2a8f01c3fcad$c4a70dd0$a001280a@phx.gbl... > Hi, > > I nee...

Seperate words in a field
I have data that has information in a field that I need to separate. Example: Jimmy Ray (2004) (director) (producer) Jenny Rich (2000) (Actress) From this example I need to separate the items in parentheses from the name and enter them all in different columns. Is there a query or other possible way to do this? Thanks for any help given. As I see it this would be a 2-step process.. First you would separate all words: Divide text across cells Select the range of cells that contains the text values. The range can be any number of rows tall, but no more than one...

text boxes same size
Is there a way in Publisher 2003 to select all text boxes on all pages and modify them so that they are the same size and have the same margins? ...