Re: Sp[litting Names from Cells

Bob Phillips gave the answers below for splitting names from cells:

=LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1)

and

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))

Using these formulas on this example John A Doe results in John A and
Doe, is it possible to split it to show John / A / Doe in 3 separate
cells, I know I could use the formulas again on the John A result to
split them but I'd like to do it in 1 go

If possible could anyone explain what the formula is doing, I
understand LEFT and LEN etc but don't understand the use of ^^

Thanks

Paul


-- 
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783
View this thread: http://www.excelforum.com/showthread.php?threadid=391603

0
7/31/2005 7:19:19 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
297 Views

Similar Articles

[PageSpeed] 17

Paul, the first space is replaced with ^^ to find the second space, the
item up to the second space is then known.


Paul Sheppard Wrote: 
> Bob Phillips gave the answers below for splitting names from cells:
> 
> =LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
> ",""))))-1)
> 
> and
> 
> =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
> ","")))))
> 
> Using these formulas on this example John A Doe results in John A and
> Doe, is it possible to split it to show John / A / Doe in 3 separate
> cells, I know I could use the formulas again on the John A result to
> split them but I'd like to do it in 1 go
> 
> If possible could anyone explain what the formula is doing, I
> understand LEFT and LEN etc but don't understand the use of ^^
> 
> Thanks
> 
> Paul


-- 
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059
View this thread: http://www.excelforum.com/showthread.php?threadid=391603

0
7/31/2005 7:52:06 AM
Reply:

Similar Artilces:

VBA to move from one cell to another cell
Hi, Certain cells in a sheet needs to be filled with data. These cells are in successive rows from 10 to 49 Data is filled in cell C10 then F10 after that cell C11 then F11 C12 and F12 until I reach C49 and F49 It is annoying to use the TAB key or the mouse to move to the next cell. Is there a way to move to the next required cell just by pressing enter after filling in the data. i.e. data entered n cell C10 (press enter) go to cell F10, type the data and press enter to go to C11,then F11 ..... One method Unlock columns C and F. Protect the worksheet and use T...

Automate a macros when data in a cell changes (e.g.when using quer
Is there any way that a macro can be activated when the data of a specific cell changes? I assigned a macro to a "check box" button from the Tool Bar "Forms", and used the "cell link" from the "check box" properties, so when the data changes in the cell linked the "check box" is marked and the macro will start. So far, the "check box" gets marked when data changes but the macro doesn't start. The only way the macro starts is when I click in the "check box". Maybe you could use a worksheet event that will fire ...

Want to copy a cell down until it comes to the next nonblank cell, and then take this next nonblank cell until it comes to the next nonblank cell, and so on...
First time post-er, please be patient! I've got an excel spreadsheet which I'm trying to put into a format that's easy to filter. Before I can do that, I've got to clean up the data in one column - I'm bound by how the data dumps from another tool. There are several rows that basically relate to one grouping of information and so in this one column I have a project name (as part of a long string of text defining additional aspects of this project) and then for several rows below that, same column, I have names of people working on this particular project. (The name of th...

text gets cut in cells
when trying enter text in a cell (say the size of a letter for example) the text gets cut; only the cut version is printable, although the formula bar displays the whole text. How can I get all the text displayed in the cell (AND printed)? Cells can only print about 1024 characters, even tho they can contain about 32000 characters. In order to print/see more, use a text box. Bob Umlas Excel MVP "D B Kaufmann" <anonymous@discussions.microsoft.com> wrote in message news:c34401c47a31$9a865ea0$a401280a@phx.gbl... > when trying enter text in a cell (say the size of a >...

Inserting a file name
I have the following line of code in a macro "FINDER;C:\My Documents\MR02.IQY", Destination:=Range("A1")) Is there a way to have a box come up (with the present file in it) and ask me what file name I would like to find and then have it inserted into the code so I can chenge it if it was neccesary otherwise it would contain the present one if I would just like to update it ? The directory would always be the same, would just like the option to change the file name if needed. Thanks Hi, See GetOpenFilename or GetSaveFilename Methods. HTH --- Orlando Magalh´┐Żes Filho (...

Arrangement of text in a cell
I have text in a cell of my spread sheet. It has last name then first name. Is there a way to re-arrange this so the first name appears first?? Brett -- brettg ------------------------------------------------------------------------ brettg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33879 View this thread: http://www.excelforum.com/showthread.php?threadid=536583 If they are separated by a space: =RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)) -- Kind regards, Niek Otten "brettg" <brettg.2...

Assign a name to Excel 2007 Charts
Hi, Using Excel 2007 SP1 and JWalk chart tools version 1.0 (2002 build) to export 5 charts as PNGs. Works fine and just what I need. The challenge is that the charts are exported as Chart1, chart2 etc.. Is there a way to assign more meaningful names to the charts so that the meaningful name is picked up during export? This is a 1/10 in terms of pain. Unlike the ribbon interface which is 11/10. Thanks, Bob ...

Name change in AD leaves mailbox inaccessable...
After a users name is changed in Active Directory the user cannot connect to the mailbox thru Outlook Express 6. The user can access the mailbox through Outlook web access. Everytime the user tries with Outlook express it comes back with cannot authenticate username or password. Use full e-mail address as username. "Tim" <anonymous@discussions.microsoft.com> wrote in message news:17aa601c41e3e$d1f97a10$a601280a@phx.gbl... > After a users name is changed in Active Directory the > user cannot connect to the mailbox thru Outlook Express > 6. The user can access ...

Re: Using sheetname variable in a formula
Hello. Suppose a cell (say A1) contains the name of a valid worksheet (sa Sheet1, Sheet2, and Sheet3). I would like to use the *contents* of cell A1 in my formula to get a data on that sheet. Something like: =*SheetNameStoredInCellA1*!A23 I know *=Sheet2!A23* is valid but when I try =A1!A23, that does not work Hope someone can help -- mm ----------------------------------------------------------------------- mms's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1653 View this thread: http://www.excelforum.com/showthread.php?threadid=31386 Hi look at the f...

e-mail name vs. account name???
I just set up a new Exchange Server2003 SP2 on an existing Windows 2000 Server AD domain. Two of the users are using generic account names (i.e. associate and notebook902). These are real people named Jessica and Kathleen. E-mail addresses have been created on their accounts using their real names (i.e. jessica@domain.com and Kathleen@domain.com). Their e-mail is working fine. They receive e-mail that is sent to jessica@domain.com but when they send e-mail it shows as from associate@domain.com. How do I fix it so sent e-mail shows coming from e-mailaddress@domain.com rather than a...

VBA Help:Change Color of Cells
Dear All, How to change the color of cells? I'm confused whether to use range object or cells object. Pls give me example. Thanks Robert Lie Hi Robert, The Cells property returns a range object, so you could, for example, use either of the following equivalent instructions: Range("A1:A10").Interior.ColorIndex = 6 Cells(1, 1).Resize(10).Interior.ColorIndex = 6 --- Regards, Norman "Robert Lie" <robert.lie24@gmail.com> wrote in message news:%23vv10TVIGHA.2928@TK2MSFTNGP10.phx.gbl... > Dear All, > > How to change the color of cel...

countif, but only using partial cell contents in the reference array #4
while it doesn't apear to be exact, i extracted the part that was givin the error in Excel, and the cell references have been updated in it t reflect the actual, not the simple i started with... but much thanks, jare -- jared ----------------------------------------------------------------------- jaredh's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1553 View this thread: http://www.excelforum.com/showthread.php?threadid=27105 To workaround the #VALUE! errors if there are cells within the source range A1:A100 which may not contain any "{", thi...

how do I attach a jpeg to a cell
I am trying to have a button or link in a cell that when clicked will open up a jpeg that is also attached to (part of) the excel file. On 5/17/2010 11:14 AM, Mark wrote: > I am trying to have a button or link in a cell that when clicked will open up > a jpeg that is also attached to (part of) the excel file. How about a hyperlink to a jpg that ISN'T attached to the Excel file? That's easy... Bill ...

2007 Excel Protecting a cell
I found help on this for 2003, but does not work the same for 2007. I want to protect a format in a cell but still allow data entry. How can I do this? -- Linda K Unlock the cells. When you protect the sheet do not allow "Format Cells" Gord Dibben MS Excel MVP On Mon, 1 Feb 2010 09:58:02 -0800, LindaAlex <Lindak@discussions.microsoft.com> wrote: >I found help on this for 2003, but does not work the same for 2007. I want >to protect a format in a cell but still allow data entry. How can I do this? That was a easy fix!! Thanks -- Linda K...

Formulas within Cell References #2
I tried to do that but it will not work in the actual cell reference I'm stumped. humejap Wrote: > Have you tried the formula > > =Concatenate("$J$",H1) > > > -- > humejap > ------------------------------------------------------------------------ > humejap's Profile > http://www.excelforum.com/member.php?action=getinfo&userid=5506 > View this thread > http://www.excelforum.com/showthread.php?threadid=39509 -- jhockstr I missed the original question, but whilst =Concatenate("$J$",H1) will join to give $J$ joined to t...

Copying cells downward on sheet
Is there a way to copy rows and colums down and keep the row height and width the same? no, you can paste column widths but no row heights. you can paste just about anything else but not row heights. >-----Original Message----- >Is there a way to copy rows and colums down and keep the row height and width >the same? >. > If you want to retain the destination row / column size, select Paste Special | Values from the Edit menu. -- Rob van Gelder - http://www.vangelder.co.nz/excel "danlinksman" <danlinksman@discussions.microsoft.com> wrote in message new...

conditional formatting of cells based on result in hidden cell
I have a column (Z) that reflects status for each record (rows). I want to have conditional formatting based on the content in column AA (which is hidden - but has the calculations). I have four options that I need to be able to develop a corresponding color for for the cells in Z. My understanding of other code examples that have been posted using Target is that they don't apply to looking at one cell and formatting a different cell. TIA, Papa J Depends on how the code is written: If Target.Column = "A" Then If Target.Value = 1 Then Range(&...

Count of text in cells
I have problem to solve,I have a column with office. The text in the Office cells with be, Office 1@810@NT but I now have a new columm with text in the following format:Test The Cell - Employment cells can contain mutiple values, Test The Cell - Education;Test The Cell - Employment;Test The Cell - Health . These values are separated by a ; NOt only do I need to count each occurance, but I then need to have a count of each one. I can have these values referred to in a cell if that makes it easier. Sorry if I have not been clear Submitted via EggHeadCafe - Software Developer Portal of Ch...

How to remove validation on entering
When i select a cell in Excel Sheet and enter - (dash/minus) sign.After clicking on sheet it will convert the previous cell into a formula (since it takes - as formula for any format selected), indicating cells index. How i validate a cell not to perform the above feature. On pressing tab or enter key will solve the probs for text format but how i validate it for the same i.e. on what event? :confused: -- shiprasharma ------------------------------------------------------------------------ shiprasharma's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27651 Vie...

Worksheet tab names
Is there an automatic way to rename worksheet tabs as per cell content. For example in cell C2, if there is name Smith i would like to rename the worksheet as Smith. Thanks in advance. Regards Jed Code ------------------- Sub renamer() ActiveSheet.Name = Range("C2").Value End Su ------------------- Hope this helps, Wil ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financia...

Click on graph bar to execute a double-click in a pivot table cell
Excel 2002. I have a group of 6 different stacked bar charts built from parts of a pivot table. Each graph has several bars with each bar having sections for categories "10", "20", "30", "40", and "50". The graphs can be direct linked to the pivot table (as a pivot chart) or linked to an intermediate sheet looksup the data from the pivot table. I want to be able to click (double or single click) on a graph bar or section of a bar and execute the same action (.ShowDetail) that occurs when you double-click on a cell in the pivot table (print th...

Changing sender's name in Outlook Express
The Outlook Express program for our Lodge was set up by our former secretary. HIS name appears as the sender on all email I send out. How can I change this so that our Lodge name will appear, and not any individual's name? Would appreciate an email reply to lodge99@(remove)cell2000.net as well as a posting if possible. Thanks! Mike You can change this in your account settings; Tools-> Accounts Note that this is an Microsoft Office Outlook forum. Outlook Express is family of Internet Explorer and Outlook of the Office family. Here is the link for the right forum http://com...

Special Characters in Client/Organization Name
MS CRM does not allow some special characters in the Organization Name. What is the best practice while installaling for a client with both a period and a comma(Sample Company, Inc.)? I will go ahead with 'Sample Company Inc.' Let me know if you have any additional insights on this topic. "Shikhar" wrote: > MS CRM does not allow some special characters in the Organization Name. What > is the best practice while installaling for a client with both a period and a > comma(Sample Company, Inc.)? Just don't use "Inc." part. Use "Sample Company&...

dynamically enter name of last modified by in spreadsheet on save
Is there a way to have excel change the name in the footer of a spreadsheet to the name of the person who last modified the the spreadsheet and also the time of the last change. Not the time it was last viewed... but specifically modified. You could write code for the BeforeSave event (more on that can be found in online VBA help) to change the footer. That would assume that the user enabled macros when the workbook was opened. If he didn't then no macros will run and you cannot enforce this. -- Jim "HW" <HW@discussions.microsoft.com> wrote in message news:17C4...

Can you use a hanging indent in an Excel cell?
I am trying to create a list in Excel but some descriptions are lengthly. It would be easier to distinguish between each item if, as a description wraps in the cell, a hangin indent was in place. Hi DBTI Hanging indents no, indents yes To create a hanging indent look alike, type five spaces before your text. "DBTI" wrote: > I am trying to create a list in Excel but some descriptions are lengthly. It > would be easier to distinguish between each item if, as a description wraps > in the cell, a hangin indent was in place. ...