Text String Separation

Is there a function opposite of "CONCATENATE"? I have a list of names
with the first and last names in the same cell. Doesn't do well if you
want to sort by last name. I diregress, rather than re-typing the whole
list I would like to know if anyone knows how to split the string at a
'space'.

Thanks for your help.

0
jrhughson (1)
12/28/2005 10:00:31 PM
excel 39879 articles. 2 followers. Follow

3 Replies
417 Views

Similar Articles

[PageSpeed] 8

Data>Text to columns
See HELP for more details, if it is not self-explanatory already

-- 
Kind regards,

Niek Otten

"TexasLegend" <jrhughson@gmail.com> wrote in message 
news:1135807231.369309.264310@f14g2000cwb.googlegroups.com...
> Is there a function opposite of "CONCATENATE"? I have a list of names
> with the first and last names in the same cell. Doesn't do well if you
> want to sort by last name. I diregress, rather than re-typing the whole
> list I would like to know if anyone knows how to split the string at a
> 'space'.
>
> Thanks for your help.
> 


0
nicolaus (2022)
12/28/2005 10:06:11 PM
You can use the Text To Columns tool on the Data menu.


-- 
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"TexasLegend" <jrhughson@gmail.com> wrote in message 
news:1135807231.369309.264310@f14g2000cwb.googlegroups.com...
> Is there a function opposite of "CONCATENATE"? I have a list of 
> names
> with the first and last names in the same cell. Doesn't do well 
> if you
> want to sort by last name. I diregress, rather than re-typing 
> the whole
> list I would like to know if anyone knows how to split the 
> string at a
> 'space'.
>
> Thanks for your help.
> 


0
chip1 (1821)
12/28/2005 10:06:42 PM
Maybe you could use 
Data|Text to columns
delimited (by space)

But watch out for 3 name names:  John Fitzgerald Kennedy



TexasLegend wrote:
> 
> Is there a function opposite of "CONCATENATE"? I have a list of names
> with the first and last names in the same cell. Doesn't do well if you
> want to sort by last name. I diregress, rather than re-typing the whole
> list I would like to know if anyone knows how to split the string at a
> 'space'.
> 
> Thanks for your help.

-- 

Dave Peterson
0
petersod (12004)
12/28/2005 10:13:39 PM
Reply:

Similar Artilces:

Crosstab ordering string
I have a crosstab query where the Column heading is 'Wait: IIf([Clock Stops in Period Flatfile]![Wait(Weeks)]<0,"0",IIf([Clock Stops in Period Flatfile]![Wait(Weeks)]>52,"52+",CStr([Clock Stops in Period Flatfile]![Wait(Weeks)])))' I used CStr so that I could have the '52+' criteria, but now that the returned value is a string, the ordering goes to pot as 6,7,8 and 9 is at the end of the query. How can I get the columns ordered, but with '52+' at the end? You can specify the column headings which will specify the order. You will have t...

What is simple text?
When creating a custom list that includes a column of accounting formated data, I get an error message that 'fields without simple text will be ignored'. The result is all other data except the accounting formatted data. Help!!!! I suppose it means without the currency symbol and thousands delimiters. On Fri, 8 Apr 2005 11:01:04 -0700, TexMas <TexMas@discussions.microsoft.com> wrote: >When creating a custom list that includes a column of accounting formated >data, I get an error message that 'fields without simple text will be >ignored'. The result is ...

How to get reference to a range as string?
I can get a reference to a cell as string with function CELL("address", A1), which will return the string $A$1. How do I get a reference to a range, for instance, A1:A5? Thanks. How will you be using it. I expect you would have to use a user defined function. MsgBox Range("mrange").Address --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm <jtan@alum.mit.edu> wrote in message news:1105666373.222974.16847...

Extracting text from a string
Hi, Can anyone help me with a formula to look through a string and find a word and then add up the number it finds in a column next to the string containing the word. eg. find Ric Col A Col B `Ric 1000 Bob 1200 Ric 1100 Dave 950 Ric` 1050 So it would report a total for Ric as 3150 Column A and B are on sheet1 and word to search for comes from sheet2. Many thanks Ric =SUMIF(ColA_Range,Sheet2!A1,ColB_Range) Where "Ric" is in Sheet2!A1 -- Return email address is not as DEEP as it appears "ric" <agwclan999@yahoo.co.uk> wr...

Money 2004: separate dates for transfers between 2 accounts?
Money requires that you have a single date for a transfer between 2 accounts. However, in reality, this is often not the case. For example, when I pay my credit card bill online, the credit card company uses the date I entered the info online (e.g., 10/1/2003). However, the money might not actually be withdrawn from my checking account for a few days (e.g., 10/6/2003). So, from my credit card statement's point of view, the transfer occured on 10/1. However, from my checking statement's point of view, the transfer occured on 10/6. Money simply uses whichever date was input la...

Unicode-aware String Decorator [new version]
This is the new version of the macro. It is smarter than first version, in fact it excludes strings in comments, or string already decorated, or the #include "MyHeader.h" stuff, etc. I've tried to develop the code in a modular way, with classes, so some part could be reused in other projects/macros. It seems to work on VS.NET2003; I don't know if it works also in VS2005 (I don't have it). I don't know if VS automation exposes the list of all strings in the document; if such string collection were exposed, a lot of code I have developed would be useless :) be...

Splitting up text in a cell
I have a worksheet extracted from another program with financial data in it. One of the columns contains information about what money has been spent on. This consists of a category and a subcategory. For example one cell might read "Bills : Electricity" or "Going out : Restaurant". The category and the subcategory are always separated by one space, a colon, then another space. What I'd like to do it separate the category from the subcategory by using a formula that can extract the text either before or after the space-colon-space bit. Having looked at some of Excel'...

How do I delete spaces from the end of text
Some of the text fields that I have to clean up have a space (or more than one space). I need to clean this text up so the last word is the end of the text string. Any ideas? Thanks Id the text copied from the web, in that case you might have trailing html characters, if not us =TRIM(A1) otherwise =TRIM(SUBSTITUTE(A1,CHAR(160),"")) if there are many see: http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Regards, Peo Sjoblom "George" <George@discussions.microsoft.com> wrote in message news:183AF579-A860-4D66-AFF8-892ED921300D@microsoft.com... &g...

Text fields displayed as scientific Notation
I am current working with Access 2002 and have several reports created... On both reports I have the same data to display... A number that is stored as text and can be up to 17 characters long. On one report it displays correctly, the other it displays as scientific Notation... I've checked all the fields, and the are define exactly the same... They are in different positions on the detail line, but have the same input fileds (Text 17 characters)... If the number is less than 17, it displays correctly.. I've doubled the size of the report field, but doesn't do a...

How do I write text on a bar chart using VBA
I have a worksheet with various bar charts. I want to write text inside the bar but cannot figure out the correct control name to modify the text. Any help is appreciated You can add a data label to the data point. With ActiveChart.SeriesCollection(1).Points(1) .HasDataLabel = True With .Datalabel .Caption = "Hello" .Position = xlLabelPositionCenter .Orientation = xlUpward '' and more if desired End With End With - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Barney wrote: > ...

Repeating numbers amd text ( content of the text box)
Hello, Using crosstab query, my report shows like this: Position Seq No. Position Year 2007 1. Elder Jason 1. Edler Mathew 2. Deacon Mark 2 Deacon John. How can I make it to show up like this: 1. Elder Jason Mathew 2. Deacon Mark John In other word, we do not want it to repeat the same number. With many thanks, -- H. Frank Situmorang Set the Hide Duplicates property of the Position text box. Where you also want to...

Floating Text Box in Excel 2K3/2K7
I am wanting to create a floating text box in Excel 2003/2007 but am currently stuck. The idea is that I will be able lock a text box with a few bullet points in the top right of the screen and that it will remain visible in that position regardless of where I scroll in the worksheet. Locking cells instead, or using Headers/Footers is not an option here. For this purpose, the text box has a name of "Box". I found this code online (along with several other examples) and replaced the name of the text box with my own, but could not get it (or any other example) to work. T...

convert per cent text to number
in access query: i have a text field that express percent as ex. :2.5% I want to convert this to a number. I've tried changing the underlying table's (a linked table) field type to numeric but get whole/rounded numbers even when I attempt to express it with multiple decimal places. How can I convert by using a query? It is probably already a number! just formatted to show a % FORMAT, but format is make-up for what is displayed. The "real" stuff is pr0bably already a number ( a value between 0 and 1, quite likely). What leaves you on the impression it is not a numbe...

Assigning XML string as the content of an attribute
Hi. I want to assign an XML string to an XML attribute. This XML string must undergo "escape" conversion so that the < and & symbols are converted in to escaped equivalents. Does the .Net library have a conversion method that does this? Note that I want to construct the resulting XML string myself without having to use an XmlDocument. E.g. string myXml = "<root><record/><record/></root>"; string myDoc = "<doc myXml='" + Converter.escapeMyXml (myXml ) + "'/>"; -- McGeeky http://mcgeeky.blogspot.com ...

How do I add a " at the end of every cell text...
I have 700 rows which I want to add a " at the beginning of column C and the end of column D, but when I do =&"""C1 and =D1&""" Excel complains :( Oh yes, I'm then looking to combine them so I have the formula =C1&", "&D1 so I'll end up with "text, text" in E... I hope it's going to be something very simple and that I'm not going to have to do it manually ?! :( TIA Nick NickTheBatMan formulated the question : > I have 700 rows which I want to add a " at the beginning of column C > and the end ...

Creating a text only catalog using data from an Excel spreadsheet
I am trying to create a Publisher catalog using data from an Excell spreadsheet. I have been able to use mail merge to format the information but it only lets me view one row of data. The rest are available using a scroll bar. How can I get all of the data to appear at one time? After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Gail <anonymous@discussions.microsoft.com>... > I am trying to create a Publisher catalog using data from an Excell > spreadsheet. I have been able to use mail merge to format the > information but it only lets me view one r...

Office 2003 SP3
Guys, I suspect we have a problem comming from the SP3 of Office 2003. The text import wizzard has some kind of internal logic to dynamically select the File origin (code page). For example: for the 2 txt files below it will chose 2 different code pages: For VED.TXT wizard will choose 1251 Cyrillic (Windows) For VED2.TXT wizard will choose 1254 Turkish (Windows) It is not a problem if user opens the txt file manually - s/he than has the opportunity to specify the code page correctly in case the wizzard has chosen the wrong one. The problem comes when other programs (like our on-line b...

excel 2000
I recently read a posting about being able to control the tab key t jump to any specified cell by using a named range... this is great! I it possible to do the same thing using text boxes -- Message posted from http://www.ExcelForum.com ...

copying text into XL & text to columns
Hi, I have a large text file that I'm wanting to convert to a table. I was hoping to simply paste it into Excel & then split the fields using Text to Columns. The problem I'm having is - when I paste the data in, instead of putting it all into one column (thus allowing me to use Text to Columns), it pastes it across 9 columns, with no actual logic to how the fields have been delimited compared to the original file. Is there any way I can force it to paste how I need it - into just one column. Any help greatly appreciated.......Regards, Jason Have you tried to open it...

How to count the occurences of a text string within a cell...
I'm trying to count the number of text-strings, in this case commas, within a cell. For instance: A1(text)=AB,BC,CD,DE,EF If I could count 4 commas, I'd know there were (4)+1 objects in the cell... Thanks in advance. I always research as much as possible before asking the forum. The forum has ALWAYS come through with the answer! --- Message posted from http://www.ExcelForum.com/ Something like: =LEN(A1)-LEN(SUBSTITUTE(A1,",","")) or more generic: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"SOMETEXTHERE",""))/LEN("SOMETEXTHERE") &q...

TOC removal and Text preserve
How can I remove Word field codes format and highligh, while preserving text with page numbers that was generated? What exactly is it that you wish to do? Do you want to display the field results? - ALT+F9 Do you want to display without the field shading? - Word Options > Advanced > Show Document Content > Field Shading > When selected (or Never) - In earlier Word versions the setting is in Tools > Options > View Do you want to convert the field to text? - Select then CTRL+SHIFT+F9. -- <>>< ><<> ><<> <>>< >&...

Need formula to Identify Cells that contain both Numbers and Text
Hi, I have a spreadsheet that has some cells in a column containing both text and numbers, i.e. a phone number, city and state. In that same column I have cells that only contain a city and state. I need to come up with a formula that will identify which cells do not contain numbers in them. Please advise, Steve Steve If the number is always first then in a column near it (We'll say the number/text is in column A enter) =IF(ISNUMBER(VALUE(LEFT(A1,1))),"Number","Text") Some may be overkill but I add VALUE to make sure it tries to parse the first character to a...

Problem with Connection String in MS office Chart 11.0
I am trying to add a chart on a form in MS Access. (.mdb) I am using Access 2003. Adding MS office chart to my form, I need to define the connection string. The data I want to use in the chart is stored in the same access database. What should be the value of the connection string.. If "XX" is the name of the table containing the data I need to use to plot the chart, what I should input as value for connection string property and what I should input as value for commandtext property. Thanks a lot for your help... ...

text not printing
Since the blaster worm hit I have office products that will not print text items. Does anyone know of a fix? thanks ...

How do I get numbers to extend into unfilled cells like text
you know how if you type text into a cell if there is nothing in the cells next to it it will overflow into those cells so that you can see all of the text. Why dont numbers work the same way. Even if the cells are empty I cannot get numbers to overflow into the open cells. does anyone know what I have to do to get this to work? Merging the cells is not an option, due to the formula im using. --- Message posted from http://www.ExcelForum.com/ I do not know of any way to get the numbers to continue into an adjacent (even empty) cell. You might try: Format Columns Autofit that might ...