Text to Columns 06-01-10

Hi.

I have a column where I have names and surnames. I want to put name in one 
column and surname in anoter column. In order to do this, I use Text to 
Columns, but I have the following issue: if the cell contains for example 
Juan Pérez, it works ok, but when the cell contains Juan De los Santos, it 
assumes there are three surnames, and then put me one word in each column.

This is the result, with the example mentioned:
A                B                C            D
Juan           Pérez  
Juan           De              los           Santos

How can I indicate that I want De los Santos, all in one cell?

Thanks in advance

Regards,
Emece.-

0
Utf
6/1/2010 4:52:01 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
1090 Views

Similar Articles

[PageSpeed] 22

Instead of using Text to columns, I'd do this via formulas:
First Name:
=LEFT(A2,FIND(" ",A2)-1)

Surname:
=MID(A2,FIND(" ",A2)+1,999)

-- 
Best Regards,

Luke M
"Emece" <Emece@discussions.microsoft.com> wrote in message 
news:8448554F-C2F1-4393-B59E-C6D58C9A05B6@microsoft.com...
> Hi.
>
> I have a column where I have names and surnames. I want to put name in one
> column and surname in anoter column. In order to do this, I use Text to
> Columns, but I have the following issue: if the cell contains for example
> Juan P�rez, it works ok, but when the cell contains Juan De los Santos, it
> assumes there are three surnames, and then put me one word in each column.
>
> This is the result, with the example mentioned:
> A                B                C            D
> Juan           P�rez
> Juan           De              los           Santos
>
> How can I indicate that I want De los Santos, all in one cell?
>
> Thanks in advance
>
> Regards,
> Emece.-
> 


0
Luke
6/1/2010 5:47:43 PM
With: Juan De los Santos in cell A1 -
* In cell C1 Type: =TRIM(RIGHT(A1,FIND(" ",A1)+1))
* In cell B1 type: =SUBSTITUTE(A1,C1,"")
Micky


"Emece" wrote:

> Hi.
> 
> I have a column where I have names and surnames. I want to put name in one 
> column and surname in anoter column. In order to do this, I use Text to 
> Columns, but I have the following issue: if the cell contains for example 
> Juan Pérez, it works ok, but when the cell contains Juan De los Santos, it 
> assumes there are three surnames, and then put me one word in each column.
> 
> This is the result, with the example mentioned:
> A                B                C            D
> Juan           Pérez  
> Juan           De              los           Santos
> 
> How can I indicate that I want De los Santos, all in one cell?
> 
> Thanks in advance
> 
> Regards,
> Emece.-
> 
0
Utf
6/1/2010 6:11:01 PM
You may try also the Text to Columns feature.
In step 2 of 3 drag out(!) the marked vertical divider and press "Finish".
http://img69.imageshack.us/img69/4248/nonamef.png
Micky


"Emece" wrote:

> Hi.
> 
> I have a column where I have names and surnames. I want to put name in one 
> column and surname in anoter column. In order to do this, I use Text to 
> Columns, but I have the following issue: if the cell contains for example 
> Juan Pérez, it works ok, but when the cell contains Juan De los Santos, it 
> assumes there are three surnames, and then put me one word in each column.
> 
> This is the result, with the example mentioned:
> A                B                C            D
> Juan           Pérez  
> Juan           De              los           Santos
> 
> How can I indicate that I want De los Santos, all in one cell?
> 
> Thanks in advance
> 
> Regards,
> Emece.-
> 
0
Utf
6/1/2010 6:58:01 PM
Your method works only with fixed width Micky.

Which is OK if all first names are same length.


Gord Dibben  MS Excel MVP

On Tue, 1 Jun 2010 11:58:01 -0700, ????? (????) ?????
<micky-a[atsymbol]tapuz[dot]co[dot]il> wrote:

>You may try also the Text to Columns feature.
>In step 2 of 3 drag out(!) the marked vertical divider and press =
"Finish".
>http://img69.imageshack.us/img69/4248/nonamef.png
>Micky
>
>
>"Emece" wrote:
>
>> Hi.
>>=20
>> I have a column where I have names and surnames. I want to put name in=
 one=20
>> column and surname in anoter column. In order to do this, I use Text =
to=20
>> Columns, but I have the following issue: if the cell contains for =
example=20
>> Juan P=E9rez, it works ok, but when the cell contains Juan De los =
Santos, it=20
>> assumes there are three surnames, and then put me one word in each =
column.
>>=20
>> This is the result, with the example mentioned:
>> A                B                C            D
>> Juan           P=E9rez =20
>> Juan           De              los           Santos
>>=20
>> How can I indicate that I want De los Santos, all in one cell?
>>=20
>> Thanks in advance
>>=20
>> Regards,
>> Emece.-
>>=20

0
Gord
6/1/2010 9:55:38 PM
Thanks to all for your helpful replies.

I need a little extra help.

I noticed that there are some cases in which I have two names also.

So I have the following options: two names and one surname, one name and two 
surnames, and two names and two surnames. 

Examples:
John Steven Jackson
John Jackson
John Jackson Ville

Which formulas are suitable for all options? I want to obtain names in one 
cell, surnames in other cell.

Thanks again.

Regards,
Emece.-


"Gord Dibben" wrote:

> Your method works only with fixed width Micky.
> 
> Which is OK if all first names are same length.
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Tue, 1 Jun 2010 11:58:01 -0700, ????? (????) ?????
> <micky-a[atsymbol]tapuz[dot]co[dot]il> wrote:
> 
> >You may try also the Text to Columns feature.
> >In step 2 of 3 drag out(!) the marked vertical divider and press "Finish".
> >http://img69.imageshack.us/img69/4248/nonamef.png
> >Micky
> >
> >
> >"Emece" wrote:
> >
> >> Hi.
> >> 
> >> I have a column where I have names and surnames. I want to put name in one 
> >> column and surname in anoter column. In order to do this, I use Text to 
> >> Columns, but I have the following issue: if the cell contains for example 
> >> Juan Pérez, it works ok, but when the cell contains Juan De los Santos, it 
> >> assumes there are three surnames, and then put me one word in each column.
> >> 
> >> This is the result, with the example mentioned:
> >> A                B                C            D
> >> Juan           Pérez  
> >> Juan           De              los           Santos
> >> 
> >> How can I indicate that I want De los Santos, all in one cell?
> >> 
> >> Thanks in advance
> >> 
> >> Regards,
> >> Emece.-
> >> 
> 
> .
> 
0
Utf
6/2/2010 6:10:45 PM
Reply:

Similar Artilces:

VBA solution to paste text into megred cells
I do realize that merged cells are bad news. I'm stuck with an Excel 2003 worksheet, which contains this dreaded feature. Users are complaining that on trying to paste text into merged cells gives Excel Warnings: "Data on the Clipboard is not the same size and shape as the selected area. Do you want to paste anyway?" And: "Cannot change part of a merged cell." Interestingly, though if the user hits F2, which places the cursor in the formula bar, then the paste operation works! I'm usually reasonably of Ok with VBA; but this ...

OUTLOOK 2007
I purchased new notebook from Dubai having Vista Home. While uising OUTLOOK 2007, the email messages make and receipts in Plain text receive in Right to Left Direction. My requirment is default text direction as Left to Right. I think problem is coming becuase of arabic or Urdu language which is set in Window Vista. I have tried all option i.e. set language as English, key board as US, email template as left to right direction but this problem is not solved till todate. I request Microsoft or member in Discussion group to help me for solving this problem. Tools, options, left to ri...

How to customize Outlook 2003, e.g. remove button text in toolbar?
I cannot find where to do that :( Thanks, Tien, Right click on the toolbar, select Customize, then right click on the button, select Default style. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Do Quyet Tien asked: | I cannot find where to do that :( | Thanks, | Tien, Thanks, but why is it easy like customize IE toolbars, just select option "No text labels...

2008 SBS questions... 07-04-10
I am trying to get a 2008 sbs running as efficiently as I can & was wondering about a couple of things. 1) I know it wants to be the DHCP server on a network, but what about for wireless clients? I have a wireless sonicwall TZ-170 for wireless laptops to connect to. They really don't need network access, just internet, so can I leave this running, as long as the addresses it hands out aren't in my same network? 2) Although the bought an SBS, the customer is not & probably won't be running exchange for a while. Can I remove exchange all together? I really see no rea...

pulling data 04-27-10
I'm not sure I am explaining this right I want the data from C21 on sheet 1 in a cell on sheet 2 if the A & B column data matches SHEET 1 row 21 A B C D E 4/21/2010 34287 74 3 4 SHEET 2 row 20 A B C D E 4/21/2010 34287 74 try =sumproduct((s1!a2:a22=a2)*(s1!b2:b22=b2)*s1!c2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Norm" <Norm@discussions.microsoft.com> wrote in message news:A40C8E08-721F-47BB-A73C-458AC...

How to substitute for a non-existing column in a joined table
Hi, Is there a simpler way than a UNION to return a default value of a joined table for which a corresponding row does not exist? The following example (not a working one, of course) illustrates what I'm after. I'd like to return 'N/A' as c2name if there is no matching row in t2 SELECT t1.c1 (CASE t2.t1pk WHEN NULL THEN 'N/A' ELSE t2.name END) AS c2name, FROM t1 LEFT JOIN t2 ON t2.t1pk = t1.pk Thanks. On 2010-04-21 21:05, bob wrote: > Is there a simpler way than a UNION to return a default value of a joined > table for which a ...

80070490 Error: Windows update encountered an unknown error 03-04-10
3 security updates will not complete (now 4). Error Code 80070490 02-Mar-10 I am unable to do these three security updates: KB970238(release date 6/9/09), KB97451(release date 10/13/09), KB954155(release date 10/13/09), & KB975517(release date 10/13/09). The last succesful update was KB9937286 on 2/27/2010 I have a Compaq Presario PC: SR5214X. OS Name Microsoft® Windows Vista™ Home Basic Version 6.0.6002 Service Pack 2 Build 6002 OS Manufacturer Microsoft Corporation System Manufacturer Compaq-Presario System Model GV441AA-ABA SR5214X System Type X86-based PC ...

Custom reports 06-13-05
How can I make custom reports in MS CRM? Thanks, M Where can I find Crystal reports? "Mikkhail" wrote: > How can I make custom reports in MS CRM? > Thanks, M "Mikkhail" <Mikkhail@discussions.microsoft.com> wrote in message news:22C1DD57-5461-4924-8CA9-55810497D2D8@microsoft.com... > Where can I find Crystal reports? You need to buy Crystal Reports Developer or Professional Edition v9.2.2. to make custom reports in Microsoft CRM > > "Mikkhail" wrote: > >> How can I make custom reports in MS CRM? >> Thanks, M you may ...

publisher 97 #10
I have a file that was oriniated in Publisher 97. I inadevertently opened and saved it in Publisher 2002 on a different computer(using server). Now I can't access it through 97. Is there a way to do this? Can't save as, as the files of type don't say 97. Maybe they were the same as Publisher 98 files. Worth a try. -- Don Vancouver, USA "Terry" <Terry@discussions.microsoft.com> wrote in message news:D0B451AE-ACF2-449B-8F11-4F0C16297A12@microsoft.com... > I have a file that was oriniated in Publisher 97. I inadevertently opened > and saved it in P...

Running Office 2001 on Mac OS 10.2, help
This might be a dumb question, but I don't want to screw things up. I was running Mac OS 9.0.4 and I downloaded OS 9.1, 9.1.2, 9.2.2 and then the new 10.2. However, all my software is still in the 9.1 classic. I want to use it all in the new OS 10.2. Will Office 2001 Academic Edition work in OS 10.2? Do I need to uninstall it in OS 9.1 first, then install it is OS 10.2? Or, can I just drag and drop it on the 10.2 desktop or in the applications folder? I have a lot of other expensive softwares in OS 9.1 that I don't want to have to upgrade, but need to be able to use in OS 10.2. Wi...

FORMATING COLUMNS..... HELP
I need to format columns to allow only 7 characters and the rest of the data to go into column B -- JTEFUN "JTEFUN" <JTEFUN@discussions.microsoft.com> wrote in message news:6C5C7A99-E83F-430E-9576-6D1DB57B9311@microsoft.com... >I need to format columns to allow only 7 characters and the rest of the >data > to go into column B > -- > JTEFUN > If you mean that if a user types a lot of data into the one cell and that when they reach 7 characters the rest of the data is automatically inserted into the next cell, then I don't think you can do that....

Rows and Columns Settings Problem
How do you set rows and columns in a way that when you scrol down/column you can always see a certain row(s)/column(s) -- Message posted from http://www.ExcelForum.com Check out XL Help for "Freeze Panes" In article <JMorgan.1ad5vf@excelforum-nospam.com>, JMorgan <<JMorgan.1ad5vf@excelforum-nospam.com>> wrote: > How do you set rows and columns in a way that when you scroll > down/column you can always see a certain row(s)/column(s). Thank -- Message posted from http://www.ExcelForum.com ...

Question re:clustered column w/3D visual effect
I have Excel 2003 Why wont the 3D chart allow you to drag it more open? There is a large open area between the left side and the axis that looks jerky :) In a plain clustered column chart you can click inside to make the frame appear and drag it larger or smaller as you desire. In the 3D, clicking only allows you to change the angles of the 3D box. Am I missing something? thanks, Meenie This should be a hint to avoid the 3D effects. 3D charts are inflexible, but more important, the 3D effects mask the information in the chart. - Jon ------- Jon Peltier Peltier Technical Services, Inc....

BULK Conditional Formatting
I've read the posts on conditional formatting for cell colour based o another cell's value (eg. set the conditional formatting to "formul is" and then "=A1>0" and set the colour as red / blue / whatever...) however wondering if I can do this for an entire column withou individually changing the conditional formatting for each cell one b one (as there are over 400 rows). Basically I need a formula that reads the contents of column B for th particular row that is active. Can anyone help -- Rob Moyl ---------------------------------------------------------------...

No Sound 03-26-10
Sorry for not crossposting - I have more problems than I can handle!!! While trying to uninstall Comodo, I had an error and the computer froze. I shut it off and it wouldn't boot - not even in Safe Mode. So I used ERUNT to fix the Registry from a command prompt. Unfortunately, the latest ERUNT file was corrupted - so I had to go back about a week. The computer then booted, but I found many changes in some of my data and settings. Also, I cannot get the sound to work. I again used ERUNT from Windows with the same backup I used from the command prompt. All indications on the ...

Charting depending on criteria & data series name as a column val
I want to chart some prices as I want to take a look at price trends. My problems, and I can not figure out how to solve them, are: 1. Is it possible to dynamically change the chart depending on certain criteria (product family & selected customers) 2. As the number of customers is variable and they are in one of the columns. Is it possible to plot a series (customer name), depending on the name of a column? 3. I want to chart the data based on the date, but just include the dates available, to prevent periods of time showing no change (e.g. I don't want monthly ticks for the x a...

Counting Texts
If cell A1 contains a paragraph of texts and if I want to count just letter "W"s (Upper or lower case), How can I achieve this? Example: A1 contains "How now brown cow" the formula should return "4". Thank you. Write a macro and use the VBA functions Instr(strName, "w") together with Split(strName, "w") and count the number of times that it finds "w" or "W" Chris "Keith" <Keith@discussions.microsoft.com> wrote in message news:CC60AA13-B569-4C55-BDC7-1B6295CDAFB9@microsoft.com... > If cell A1 co...

How do I filter rows based upon a column value
I have a spreadsheet that contains multiple agency id's in a column. When generating reports, I would like to filter per agency and display only the rows associated with that agency. Is there a tutorial or sample on how to do this? Hi It sounds like you are looking for Data / Filter / AutoFilter. Have a look here for some basics: http://www.contextures.com/xlautofilter01.html -- Andy. "Jack" <nfr@nospam.com> wrote in message news:eqiU08TVEHA.2988@TK2MSFTNGP10.phx.gbl... > I have a spreadsheet that contains multiple agency id's in a column. When > generati...

Custom Controls 10-16-04
Hi, I would like to create a custom control .ascx and I'd like to konow how can I put it on a standard CRM form (for example Accounts) Thanks in advance Zsolt There is not a supported way to do this on the standard forms themselves. The closest supported method wuld be to add a new Tab on the left of the page via teh ISV.Confi and have that display your control. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Sat, 16 Oct 2004 01:19:06 -0700, Zsolt Z�mbik <zombikzs@titanium-consulting.net> wrote: Hi, I would ...

Emails missing text
We have about 80 users on our Exchange server. We have 2 users that have an issue with reading their email. It appears that when they open the email the right side of the email/text is gone. When I shadow their session I can see the entire email but they cannot. It is as if they right section of the text of the email was deleted. I'm sorry I'm not explaining this well. Any feedback is appreciated. Thanks ...

how do i change or remove a signiture that is under outo text hea.
Someone please help i am using outlook 2003 and i am triyng to change a signiture that is under auto text. to get into it i open a message push insert then outo text then signiture. I want to change it. I have closed the untilted message gone to tools then options then mail format then signitures then remove all the signitures but the signiture under insert and outo text is still there See if this helps: http://office.microsoft.com/en-us/word/HP051860291033.aspx -- Bill R "nearly bald with frustration" <nearly bald with frustration@discussions.microsoft.com> wrote i...

text size in reading pane
Where is the font selection / text size control for the Outlook 2003 reading pane (incoming emails)? I just bought a brand new dell with windows xp and all and I can't even increase the text size of incoming emails as I could with older versions of Outlook Express. I have been trying to figure this out for around 15 hours now and have come up empty. What kind of progress is this anyway???? A program rendered unable to accomplish such a simple practical thing like that!! Im very upset. My vision is poor. Unless I get an answer soon, I am considering a return of this unit to dell...

Export excel file to semicolon delimited text file
Hello all. We have the need to export a file from excel to a semicolon delimited text file. Is there a way to do this from Excel directly? If not, does anyone have any other suggestions? TIA for your help. If you change your regional settings to use the semicolon as your list separator, you can save as .csv and it should work. But this may affect other programs, too--since it's a windows setting. windows start button|settings|control panel|regional and language options| regional options tab|customize button near the bottom. On the other hand, if you don't want to fiddle with...

columns changed to numbers instead of letters?
I noticed my excel clumns have changed from letters to numbers and forumlas now look like =RC[-1]*R[-3]C[6] I was trying to make the R[-3]C[6] static (using the $) but it errors. Thanks Mike In Excel Options uncheck "R1C1 Reference Style" Gord Dibben MS Excel MVP On Thu, 28 Oct 2010 20:25:30 -0400, Mike <no_please@not.com> wrote: >I noticed my excel clumns have changed from letters to numbers and >forumlas now look like =RC[-1]*R[-3]C[6] >I was trying to make the R[-3]C[6] static (using the $) but it errors. >Thanks >Mike ...

How do I remove cross hatches in Sigma column
Addition of a column results in cross hatches. How do I get rid of the cross hatches and simply have the sum printed in the column? Widen the cell so the resulting value will fit. Excel does this so you don't accidentally SEE a number that's LESS than the actual number, which is what would occur if the column wasn't wide enough. ************ Anne Troy www.OfficeArticles.com "YHESSLER@EXAMPLE.COM" <YHESSLER@EXAMPLE.COM@discussions.microsoft.com> wrote in message news:08545D7F-E1B3-4879-A7DB-A2B723DDCD3F@microsoft.com... > Addition of a column results in cro...