separate columns (three letter code)

Hi, does someone know how to separate text from one column into two columns?

I have columns like 
[dog, KKL]
[horse, PPN]

Everytime, three capitals in the end (which is a code).

Is it possible to separate that, and put it in another column?

Thanks in advance.
0
Utf
3/16/2010 12:15:01 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
634 Views

Similar Articles

[PageSpeed] 32

--Select the range/column needs to be changed. 
--From menu Data>Text to Columns will populate the 'Convert Text to Columns 
Wizard'
--By default the selection is 'Delimited'.
Keep the selection and hit 'Next'.  
--From the Step2 of the Wizard from the options select comma and hit Next.

Hit Finish

-- 
Jacob


"mariekek5" wrote:

> Hi, does someone know how to separate text from one column into two columns?
> 
> I have columns like 
> [dog, KKL]
> [horse, PPN]
> 
> Everytime, three capitals in the end (which is a code).
> 
> Is it possible to separate that, and put it in another column?
> 
> Thanks in advance.
0
Utf
3/16/2010 12:20:09 PM
Thanks Jacob for your help.

But actually, that will not work... I simplified my example, in real life it 
is:

[Mw. dog PKO]
[Mr. horse KLI]

So I really need a formula, to separate the three letters on the right....I 
know there is a formula, but I forgot it...

Hope you can help me out.

Thanks in advance

"Jacob Skaria" wrote:

> --Select the range/column needs to be changed. 
> --From menu Data>Text to Columns will populate the 'Convert Text to Columns 
> Wizard'
> --By default the selection is 'Delimited'.
> Keep the selection and hit 'Next'.  
> --From the Step2 of the Wizard from the options select comma and hit Next.
> 
> Hit Finish
> 
> -- 
> Jacob
> 
> 
> "mariekek5" wrote:
> 
> > Hi, does someone know how to separate text from one column into two columns?
> > 
> > I have columns like 
> > [dog, KKL]
> > [horse, PPN]
> > 
> > Everytime, three capitals in the end (which is a code).
> > 
> > Is it possible to separate that, and put it in another column?
> > 
> > Thanks in advance.
0
Utf
3/16/2010 12:34:01 PM
Hi,

maybe this

=LEFT(RIGHT(A1,4),3)
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"mariekek5" wrote:

> Thanks Jacob for your help.
> 
> But actually, that will not work... I simplified my example, in real life it 
> is:
> 
> [Mw. dog PKO]
> [Mr. horse KLI]
> 
> So I really need a formula, to separate the three letters on the right....I 
> know there is a formula, but I forgot it...
> 
> Hope you can help me out.
> 
> Thanks in advance
> 
> "Jacob Skaria" wrote:
> 
> > --Select the range/column needs to be changed. 
> > --From menu Data>Text to Columns will populate the 'Convert Text to Columns 
> > Wizard'
> > --By default the selection is 'Delimited'.
> > Keep the selection and hit 'Next'.  
> > --From the Step2 of the Wizard from the options select comma and hit Next.
> > 
> > Hit Finish
> > 
> > -- 
> > Jacob
> > 
> > 
> > "mariekek5" wrote:
> > 
> > > Hi, does someone know how to separate text from one column into two columns?
> > > 
> > > I have columns like 
> > > [dog, KKL]
> > > [horse, PPN]
> > > 
> > > Everytime, three capitals in the end (which is a code).
> > > 
> > > Is it possible to separate that, and put it in another column?
> > > 
> > > Thanks in advance.
0
Utf
3/16/2010 12:37:01 PM
Use RIGHT()

=RIGHT(A1,3)

-- 
Jacob


"mariekek5" wrote:

> Thanks Jacob for your help.
> 
> But actually, that will not work... I simplified my example, in real life it 
> is:
> 
> [Mw. dog PKO]
> [Mr. horse KLI]
> 
> So I really need a formula, to separate the three letters on the right....I 
> know there is a formula, but I forgot it...
> 
> Hope you can help me out.
> 
> Thanks in advance
> 
> "Jacob Skaria" wrote:
> 
> > --Select the range/column needs to be changed. 
> > --From menu Data>Text to Columns will populate the 'Convert Text to Columns 
> > Wizard'
> > --By default the selection is 'Delimited'.
> > Keep the selection and hit 'Next'.  
> > --From the Step2 of the Wizard from the options select comma and hit Next.
> > 
> > Hit Finish
> > 
> > -- 
> > Jacob
> > 
> > 
> > "mariekek5" wrote:
> > 
> > > Hi, does someone know how to separate text from one column into two columns?
> > > 
> > > I have columns like 
> > > [dog, KKL]
> > > [horse, PPN]
> > > 
> > > Everytime, three capitals in the end (which is a code).
> > > 
> > > Is it possible to separate that, and put it in another column?
> > > 
> > > Thanks in advance.
0
Utf
3/16/2010 12:40:01 PM
Great, this works perfectly.

Is it also possible to get the rest in another column? Like text minus 
RIGHT(A1,3)...?



"Jacob Skaria" wrote:

> Use RIGHT()
> 
> =RIGHT(A1,3)
> 
> -- 
> Jacob
> 
> 
> "mariekek5" wrote:
> 
> > Thanks Jacob for your help.
> > 
> > But actually, that will not work... I simplified my example, in real life it 
> > is:
> > 
> > [Mw. dog PKO]
> > [Mr. horse KLI]
> > 
> > So I really need a formula, to separate the three letters on the right....I 
> > know there is a formula, but I forgot it...
> > 
> > Hope you can help me out.
> > 
> > Thanks in advance
> > 
> > "Jacob Skaria" wrote:
> > 
> > > --Select the range/column needs to be changed. 
> > > --From menu Data>Text to Columns will populate the 'Convert Text to Columns 
> > > Wizard'
> > > --By default the selection is 'Delimited'.
> > > Keep the selection and hit 'Next'.  
> > > --From the Step2 of the Wizard from the options select comma and hit Next.
> > > 
> > > Hit Finish
> > > 
> > > -- 
> > > Jacob
> > > 
> > > 
> > > "mariekek5" wrote:
> > > 
> > > > Hi, does someone know how to separate text from one column into two columns?
> > > > 
> > > > I have columns like 
> > > > [dog, KKL]
> > > > [horse, PPN]
> > > > 
> > > > Everytime, three capitals in the end (which is a code).
> > > > 
> > > > Is it possible to separate that, and put it in another column?
> > > > 
> > > > Thanks in advance.
0
Utf
3/16/2010 12:52:03 PM
Yes; try

=LEFT(A1,LEN(A1)-3)

-- 
Jacob


"mariekek5" wrote:

> Great, this works perfectly.
> 
> Is it also possible to get the rest in another column? Like text minus 
> RIGHT(A1,3)...?
> 
> 
> 
> "Jacob Skaria" wrote:
> 
> > Use RIGHT()
> > 
> > =RIGHT(A1,3)
> > 
> > -- 
> > Jacob
> > 
> > 
> > "mariekek5" wrote:
> > 
> > > Thanks Jacob for your help.
> > > 
> > > But actually, that will not work... I simplified my example, in real life it 
> > > is:
> > > 
> > > [Mw. dog PKO]
> > > [Mr. horse KLI]
> > > 
> > > So I really need a formula, to separate the three letters on the right....I 
> > > know there is a formula, but I forgot it...
> > > 
> > > Hope you can help me out.
> > > 
> > > Thanks in advance
> > > 
> > > "Jacob Skaria" wrote:
> > > 
> > > > --Select the range/column needs to be changed. 
> > > > --From menu Data>Text to Columns will populate the 'Convert Text to Columns 
> > > > Wizard'
> > > > --By default the selection is 'Delimited'.
> > > > Keep the selection and hit 'Next'.  
> > > > --From the Step2 of the Wizard from the options select comma and hit Next.
> > > > 
> > > > Hit Finish
> > > > 
> > > > -- 
> > > > Jacob
> > > > 
> > > > 
> > > > "mariekek5" wrote:
> > > > 
> > > > > Hi, does someone know how to separate text from one column into two columns?
> > > > > 
> > > > > I have columns like 
> > > > > [dog, KKL]
> > > > > [horse, PPN]
> > > > > 
> > > > > Everytime, three capitals in the end (which is a code).
> > > > > 
> > > > > Is it possible to separate that, and put it in another column?
> > > > > 
> > > > > Thanks in advance.
0
Utf
3/16/2010 12:56:01 PM
Great, works perfectly! 

Thank you!

"Jacob Skaria" wrote:

> Yes; try
> 
> =LEFT(A1,LEN(A1)-3)
> 
> -- 
> Jacob
> 
> 
> "mariekek5" wrote:
> 
> > Great, this works perfectly.
> > 
> > Is it also possible to get the rest in another column? Like text minus 
> > RIGHT(A1,3)...?
> > 
> > 
> > 
> > "Jacob Skaria" wrote:
> > 
> > > Use RIGHT()
> > > 
> > > =RIGHT(A1,3)
> > > 
> > > -- 
> > > Jacob
> > > 
> > > 
> > > "mariekek5" wrote:
> > > 
> > > > Thanks Jacob for your help.
> > > > 
> > > > But actually, that will not work... I simplified my example, in real life it 
> > > > is:
> > > > 
> > > > [Mw. dog PKO]
> > > > [Mr. horse KLI]
> > > > 
> > > > So I really need a formula, to separate the three letters on the right....I 
> > > > know there is a formula, but I forgot it...
> > > > 
> > > > Hope you can help me out.
> > > > 
> > > > Thanks in advance
> > > > 
> > > > "Jacob Skaria" wrote:
> > > > 
> > > > > --Select the range/column needs to be changed. 
> > > > > --From menu Data>Text to Columns will populate the 'Convert Text to Columns 
> > > > > Wizard'
> > > > > --By default the selection is 'Delimited'.
> > > > > Keep the selection and hit 'Next'.  
> > > > > --From the Step2 of the Wizard from the options select comma and hit Next.
> > > > > 
> > > > > Hit Finish
> > > > > 
> > > > > -- 
> > > > > Jacob
> > > > > 
> > > > > 
> > > > > "mariekek5" wrote:
> > > > > 
> > > > > > Hi, does someone know how to separate text from one column into two columns?
> > > > > > 
> > > > > > I have columns like 
> > > > > > [dog, KKL]
> > > > > > [horse, PPN]
> > > > > > 
> > > > > > Everytime, three capitals in the end (which is a code).
> > > > > > 
> > > > > > Is it possible to separate that, and put it in another column?
> > > > > > 
> > > > > > Thanks in advance.
0
Utf
3/16/2010 1:21:01 PM
Reply:

Similar Artilces:

How to create a single line separated by commas from a matrix?
Hello, I've the following problem. I've a table made of several columns. Each colum represents a characteristic regarding to a subject (e.g. Name, Surname, Home Street, etc...). In the rows I put the different people. I need to export these info to a format compatible with other program - an email program-, which requires the information to be arranged in a different way. The program needs all these data to be ordered in a single row, with each characteristic between quotation marks, and separated by commas, following this pattern: "characteristic 1","characteristic ...

If column A and column B have values how do i get the total in co.
ex. A B C 1 1 1 2 2 3 1 4 3 4 1 5 4 5 1 6 It is better to express your question in the body of the message rather than the subject. However, as best I understand your question, you can use a simple formula in C1 , =A1+B1, and fill down as far in column C as you need to go. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "reness" <reness@discussions.microsoft.com> wrote in message news:3AFFE821-6E...

Totalling columns and repeating formulas in new entries
i'm working in excel 2003 i've tried searching this, but have come up with nothing. it seems s basic, i'm sure i must be calling it the wrong thing or something. in my worksheet (that's what a spreadsheet is called now, isn't it?) enter expenses in column "e" and revenues in column "i", with the ne gain or loss for that row (entry) showing up in column "j". i had n problem setting that up. the first problem i'm having is the formulas and formatting bein copied to the next entry. some of the new entries have no value i column "e&...

Help! Using VB.NET Post Transaction. What wrong with my code?
Hello everyone, I have convrerted Antonia's C# saample to VB.Net as follows in order to post a transaction into RMS Via Qsrules (for a vey critical project in our company) The database connection executes ok The batch number is being generated ok I have vaidated the items and they are okay HOWEVER NO TRANSACTION IS GENERATED Where did I go wrong with my function? Declaration: Private info as QSRules.ConnectionInfo = New QSRules.connectionInfoClass Private mySession as QSRules.SessionClass =New QSRules.SessionClass Function PostTrans() info.RegisterNumber = 1 info.Pr...

Separate inboxes for separate accounts
How can I separate incoming e-mail from separate e-mail accounts into folders so I can tell where incoming mail came from? Using Outlook 2000. >-----Original Message----- >How can I separate incoming e-mail from separate e-mail >accounts into folders so I can tell where incoming mail >came from? Using Outlook 2000. >. > I'm a novice but I just ask that question and dl@spoofmail.com gave me the following advice: It is done with rules. 1st create your folders (sounds like you've done that.) Then select Tools / rules & alerts / New Rule... This brings up...

Setting variable in API code
I am using some API code in an Access 2000 application I'm building. In this API code I have the following: ConnectToServer(ByVal strShareName As String,... The variable strShareName is being set correctly but I can not find how. No where in the project code is this variable being set. I need to adjust the visibility of this variable so I can disconnect the share from a different module. Search your code for ConnectToServer. The code will be in VBE environment (Ctrl_G) and search the code with Ctrl_F. Be sure to search the entire project. Definitively, the callin...

Master image to use in separate sheets?
Hello All, Does anyone know if there is a way to tell Excel to use the same image/background for each sheet, instead of needing to import for each one? Or is there a way to set up a (similiar to Power Point/Master slide). Thanks in advance, drew If it's just for one workbook, I'd have a master worksheet in that workbook that I could just copy. If it's for lots of workbooks, I'd set up a workbook with one sheet that had that background the way I wanted it. Then save this workbook as a Template (let the folder default to excel's favorite). Tnen when you rightclick on...

Separating a comma separated list
Not having a great deal of experience with macros, I thought I might see if I could pick the brains of the group. I have to essentially break out a comma separated text string in a single cell into a vertical list, with a reference number thrown in for good measure. Example. Data I have: A B 1 NUMBER RESOURCE 2 101 Smith John,Jones Adam,Brown Philip,Greene Thomas 3 102 Smith John,Jackson Arthur Needs to look like: A B 1 NUMBER RESOURCE 2 101 Smith John 3 101 Jones Adam 4 101 Brown Philip 5 101 Gr...

Separating strings in a field to separate fields
Hi, I have a field that consists of a string value that looks like Item1, Item2, Item3, each item is separated by commas. I want to separate that string value so that each item has its own field. Is there code that will easily do this? Thanks, Jaime On Wed, 5 Dec 2007 12:08:57 -0800 (PST), jseger22@yahoo.com wrote: >Hi, > >I have a field that consists of a string value that looks like Item1, >Item2, Item3, each item is separated by commas. I want to separate >that string value so that each item has its own field. Is there code >that will easily do this? > >Thank...

Launch Oulook Express Newsreader separately
Hi I use Outlook Express as my newsreader. My problem is that in order to access it, I need to first launch Outlook. Is there some way I can launch the newsreader portion of OE without launching Outlook? A command line switch or something I imagine. I'm using Outlook XP with OE 6 on Windows XP pro. Thanks I believe the command-line switch is /newsonly. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "aJax&...

Excel 2000 vs 97 code
Several weeks ago I inquired in this news group about how to ensure that when the end user closed a spreadsheet that all the data was entered. I got an excellent response from Ron de Bruin that worked great on my machine where I have Excel 2000 however when I took it to work it would not work, they have '97. How can I alter the following code to get it to work on '97? Thank you in advance. Private sub WorkBook_Before Close(Cancel As Boolean) If Application.WorksheetFunction.CountA(Sheets("Sheet1") ..Range("A1:A10")) < 10 Then MgsBox "You must fill i...

separate pages rather than spreads?
Hi Using 2007 I created a .pdf of an A5 booklet with 80 double sided pages and sent it to an outside printer. They replied asking "Would it be possible to send in the inside pages as separate pages rather than spreads? That is a pdf with the pages A5 size in number order 1- whatever." Anyone know how to do that please ?? -- Martin ���� Martin ���� @nohere.net wrote: > Hi > Using 2007 > I created a .pdf of an A5 booklet with 80 double sided pages and sent > it to an outside printer. They replied asking > > "Would it be possible to se...

Postal Bar Coding & Using Outlook Address Book in Publisher
I would like to add bar coding to my envelopes but can't seem to find any help in Publisher for this feature. I'd also like to find a way to use my Outlook Address book in Publisher - can anyone help me. Thanks After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Sharon Fermer <anonymous@discussions.microsoft.com>... > I would like to add bar coding to my envelopes but can't seem to find > any help in Publisher for this feature. Read www.mvps.org/the_nerd/Publisher/FAQs.htm > I'd also like to find a way > to use my Outlook Address bo...

Need to Separate
Hello All, I was wondering if there is a way to separate the following: I have the following: TX;CA;NM;VT;NY;MI;OK;FL I need it to be: TX CA NM VT NY MI OK FL Thanks, Charles Data>Text to Columns>Delimited by ; will get it into columns. Then copy>paste special>transpose will get it into one column. Gord Dibben MS Excel MVP On Tue, 16 Oct 2007 21:02:46 -0500, "Charles Reid" <creid@satx.rr.com> wrote: >Hello All, > >I was wondering if there is a way to separate the following: > >I have the following: > >TX;CA;NM;VT;NY;MI;OK;FL &g...

Synching 2 iPhones to Outlook on one PC for separate calendaring
I want to use Outlook on our new PC to synch calendars for two iPhones belonging to my wife and myself. How do I go about this to keep the calendaring info separate; how does the iPhone know which calendar to synch with? Do I have to open Outlook to the right calendar somehow before I synch with the appropriate iPhone? Or is there some automated way of doing this so that I can just synch my iPhone without opening Outlook, and the system will know which iPhone gets synched with which calendar? Thanks -- Bob You'll need to configure the sync to use the correct profile and outlook w...

how to move the cursor to column A after entering data column F
Is there a way when entering data in a spreadsheet - to have the cursor move to column A after entering data in Column F or beyond? If you enter data across the worksheet press tab, when you have entered the data in column F press the Enter key. Check also Tools | Options |Edit that you have selected Down in the Move selection after Enter. Does this work for you? -- Rae Drysdale "tskaiser" wrote: > Is there a way when entering data in a spreadsheet - to have the cursor move > to column A after entering data in Column F or beyond? try this (put in sheet module) Priva...

How to separate 2 words into separate kolumns
Hi I have just exported my address book in to Excel file, what I need to do is make from one field where I have: John Brown separate John in to column Name and remove Brown in to column surrname. I cant find any idea how to do such a thing. you can use following formulas consider a1 here as cell with complete name for first name formula =left(A1,find(" ",a1)) for surname =right(A1,len(a1)-find(" ",a1)) Regards NC you can also use menu data\text to columns feature of excel choose delimited in first window & space as separator regards NC ...

double spacing a column
I need to make a column narrow so that the title is on 2 lines You can press alt + enter to get a new line (not new row) and still use one cell -- Regards, Peo Sjoblom "bce" <bce@discussions.microsoft.com> wrote in message news:715E09B4-CA67-4EBB-BBFB-629AB970AA23@microsoft.com... > I need to make a column narrow so that the title is on 2 lines You can also go to Format>Cells>Alignment and check the box for Wrap text. Column width will control wrapping, whereas Peo's suggestion forces a line break even in wider columns where wrap would not occur even if ...

Unique data in column or row in Excel
I have a problem with posibility of repeating data in column. Of course this is normal behaviour of Excel, but I need to assure unique data in column. Is there a way to do this without an extra effort? Hi Ali, Try to use this formula in Data/Validation/Custom: =ISERROR(MATCH(A1,INDIRECT("A1:A"&CELL("row",A1)-1),0)) Regards, Stefi „Ali” ezt írta: > I have a problem with posibility of repeating data in column. Of course this > is normal behaviour of Excel, but I need to assure unique data in column. Is > there a way to do this without an extra effo...

Copy column header to next column, delete & delete every nth colum
Well SNPid Call SamID Well SNPid Call SamID Well SNPid Call SamID A01 rs10090154 C 1 A01 rs1016343 C 1 A01 rs10486567 AG 1 A02 rs10090154 C 2 A02 rs1016343 TC 2 A02 rs10486567 AG 2 A03 rs10090154 C 3 A03 rs1016343 TC 3 A03 rs10486567 G 3 Hi, My workflow was dramatically streamlined thanks to a previous question posted and the generous reply and help of members here. I have another step in my data-cleanup that requires reformatting my excel table and I would be very appreciative if I can get some help. I myself do not have the Visual Basic abilities to do this. Here is the table structur...

separating text into separate columns
I'm new to Excel and working on a document with addresses in it. However, the first name and last name of the people are within the same column. So my question is: how do you separate text into separate columns without having to delete it? For example, my text currently looks like this: "Jones, James" but I want Jones to be in a separate column (titled "LAST") and James (titled "FIRST") to be in a separate column. Help? Thanks! With a blank column to the right of the column containing your data, select teh column with the names and do Data / Text To Columns...

separating items in a cell
Hi. I have a column that has values like the following: "Doe, John H." What I want to do is put "Doe" in Column A and "John H." in column B and lose the comma. I am sure there is an easy way to do this. Any help would be appreciated! TIA! Follow these steps: 1. Go Data->'Text to Columns' 2. Choose 'Delimited' 3. Check 'Comma' -- Regards, Dave "Jenn" wrote: > Hi. I have a column that has values like the following: > "Doe, John H." > What I want to do is put "Doe" in Column A and "John ...

Three Money 2004 questions
A few questions on Money 2004 (on Windows XP) 1. Selecting to use online features used to work, but now I can't log in unless I turn off online. Is this because I didn't subscribe to some for-pay service? 2. When downloading bank checking account data (as Money OFX files) and importing into Money2004, all my checks are listed with "John Anderson" as the payee. "John Anderson" is one of my payees, but why is Money stuck on this, and how can I change the default payee to "unknown" or something like that? (BTW, I've tried, but this one stumps me). ...

Column limit for exporting
Is there a limit to the number of columns that can be exported to a fixed format text (*.prn) file? I am trying to export a large data file that contains 75 columns and approximately 17,000 rows from Excel to a fixed format text file for use with another application. I've found that column 39 on wraps around to the end of the rows for the first 38 columns. When I try and import this data to another application half the data is in the wrong columns now. I've tried using a MS-Dos text file and it does not maintain the spacing necessary. I am also using Courier New as my font ...

My charts do not show when moved as a separate chart tab...
In excel 2007 my charts are displayed when inserted in a a sheet but they do not show when moved as separate tabs ...