How do I transpose Comma Separated Data in each cell and delete t.

I need to switch a names list in each cell:
[LAST_NAME, FIRST_NAME]
to
[FIRST_NAME LAST_NAME] (no comma),
preferably into two cells:
[FIRST_NAME]  [LAST_NAME]
0
Randy1 (153)
2/15/2005 8:41:04 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
444 Views

Similar Articles

[PageSpeed] 54

Last Name:

=LEFT(A1,FIND(",",A1)-1)

First Name:

=MID(A1,FIND(",",A1)+2,999)

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I need to switch a names list in each cell:
>[LAST_NAME, FIRST_NAME]
>to
>[FIRST_NAME LAST_NAME] (no comma),
>preferably into two cells:
>[FIRST_NAME]  [LAST_NAME]
>.
>
0
jasonjmorin (551)
2/15/2005 9:44:39 PM
You may be able to just select the column of names and use Data>Text to 
Columns. Specify Comma Delimited in first & second step |:>)

"randy" wrote:

> I need to switch a names list in each cell:
> [LAST_NAME, FIRST_NAME]
> to
> [FIRST_NAME LAST_NAME] (no comma),
> preferably into two cells:
> [FIRST_NAME]  [LAST_NAME]
0
CyberTaz (411)
2/15/2005 11:07:02 PM
Reply:

Similar Artilces:

How do I create a rolling average chart, adding most recent data?
Would like to create a chart where new data is added weekly to a data table and the chart graphs this newest data and drops the oldest. Everytime I add the data to the table, the graph moves the source data reference down so that the original data is still displayed. Thanks. Check out http://peltiertech.com/Excel/Charts/DynamicLast12.html Also there are several examples of dynamic range charts at http://tushar-mehta.com Jerry Doug@NxEdge wrote: > Would like to create a chart where new data is added weekly to a data table > and the chart graphs this newest data and drops ...

Corrupt data file
I have a similar issue like "Don Alwalt" had in this thread: http://groups-beta.google.com/group/microsoft.public.money/browse_thread/thread/6202459bfbe9f003 - New transactions are not appearing in the register - Money crashes when loading accounts after downloading update. Problem is that his solution does work for me. I can't even delete the account, or disable online services. I did a Standard repair, and even tried to do a Standard repait on the output file again. Nothing helps. I am able to export the account. I don't want to create a new data file and export and...

Organizing data
Hi, i have a list like this: 2 23 27 ... 1 11 34 ... ... I need to arrenge the blocks of data in order from A to Z, so it woul look like this 1 11 34 2 23 27 Do you know how can i do this, any help would be really apriciated Thk -- sams ----------------------------------------------------------------------- samsg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1624 View this thread: http://www.excelforum.com/showthread.php?threadid=27763 Hi not really sure what't the logic behind this grouping. They is '34' in the same group as '1' a...

External data references in EXCEL 2002
I want to remove all references to external data (ODBC link to SQL server) from my workbook. I have deleted the columns that contained the data, yet the named ranges are still showing in the properties page. How do I remove all the references? This also invokes XLQUERY.XLA macro. ...

Copying data between atbs in a custom form
Hello I have a custom form. Form is a modified appointment with a second tab for a custom form. Trying to copy data from custom form/second tab to first tab which a standard Appointment page. First trying to make sure that a user is in a second tab which is called Transportation Form. When code is executed it should take data from some fields in Transportation Form and copy it over to Appointment tab/first tab. Not sure how to call tabs. Any help would be appreciated Sub Appt_From_From() Dim txtBody1 As String Dim txtBody2 As String Dim txtBody3 As String Dim objCurContact As...

transposing info.
I want to transfer information from one spreadsheet to another with different titles on the columns. How do I do this? I want to direct the values on the column which has a different title column to the sheet that I want to move it to. Thanks guideme wrote: > I want to transfer information from one spreadsheet to another with > different titles on the columns. How do I do this? > > I want to direct the values on the column which has a different title > column to the sheet that I want to move it to. > > Thanks This is more of moving data to another sheet which has di...

delete outbox email
Craig, your suggestion worked. I took my pc offlline and opened up outlook and was able to delete the large file. thanks Kris ...

IF formula with multiple data range in cell
I'm trying to create an IF formula that returns data after searching a cell that contains more than one data range. For ex: Cell B3 contains GCVW-00001, GCVW-00002 I need the formula in C3 to return Yes if B3 contains GCVW-00001 and I need the formula in D3 to return Yes if B3 contains GCVW-00002 I'm able to get the formula to return yes or no correctly if cell B3 contains either GCVW-00001 or GCVW-00002, but the formula won't work if it contains both at the same time. right now my basic formula looks like: =IF(B3="GCVW-00001","Yes","No&q...

how much is too much data for a cell?
a cell should hold 32,000 characters if i am not mistaken. i have a large spreadsheet that has codes, their use, description and the # of times they have been 'hit'. most of the information fits in a cell very easily but i have one description that is huge. i made the cell 255x409 which is the max and what happened was part of the information showed and then the rest of the cell was blank. at that cell size there was more than enough room for all the data but for some reason it would not all show. i tried things like changing the alignment and it is on wrap text. i tried selectin...

Excluding Entire Data Fields in a Graph
I am trying to figure this out and there are a lot of other posts that skirt around this subject but none that specifically address my needs. I am using a bar graph and lets say for example I am using a chart of fruit picking that looks like this: Jun Jul Aug Sep Lemons 10 15 18 7 Apples 5 10 15 29 Oranges 9 20 17 33 Now my bar graph shows three bars labeled Lemons, Apples and Oranges. I have a drop down box where I can choose "Citrus" or "All Fruit" When I choose citrus the entire row of Apples in ...

Inseting data in cells
I have a row of cells that have last names in them, is there a way I can insert some data into all of the cells, this data will be the same for all names. Ex Before jones1 After (alias=jones1) then if possible export all of the names to a word or text file all in one line. Ex (alias=jones1)(alias=smith2)(alias=johnson3) Thanks for any help!!!! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Assuming last names are in A2:Z2 Put in A3 :...

Data Access Page Un-editable
I created a DAP from a form I have built, but none of the text fields, or check boxes allow me to edit them while in the DAP. I am trying to read up on DAP's but I have not found anything that helps me understand how to set them to be editable. Can someone help? more info for you: I am using office 2003 SP3 and IE7. I have installed OWC11. I have the access database and the DAP (HTML Document) saved in the same folder as the db. "Cameron" wrote: > I created a DAP from a form I have built, but none of the text fields, or > check boxes allow me to e...

Data Validation #28
How do you change either the size of the text box or the size of the source data in a drop down text box when created through data validation? You can't change the font or font size for a Data Validation dropdown list. To make the font appear larger, you can use programming to adjust the zoom setting when the cell is selected. There's sample code here: http://www.contextures.com/xlDataVal08.html#Larger Or, there's a sample file here that provides a combobox from which you can select one of the values from the data validation list. In thecombobox, you can set the font...

Remove comma from Org Name
I planning to do a 1.2 install. Just wondering if we are still removing the comma from the org name like 1.0. Ex: Company XXX, Inc. => Company XXX Inc. Yes, we had to do that with our 1.2 install. "Tony Nguyen" <anonymous@discussions.microsoft.com> wrote in message news:b56801c3ec3a$1352fd10$a401280a@phx.gbl... > I planning to do a 1.2 install. Just wondering if we are > still removing the comma from the org name like 1.0. > Ex: Company XXX, Inc. => Company XXX Inc. ...

Transposing list of numbers
I'm using Excel 2003. I have a list of 400 numbers in Column A, I would like to transpose it across 7 columns and 58 rows. Is there an easy way to do this? I understand I can do the copy/paste special/transpose for 7 at a time. Thank you. -- jlhcat Sub ColtoRows() Dim rng As Range Dim i As Long Dim J As Long Set rng = Cells(Rows.Count, 1).End(xlUp) J = 1 On Error Resume Next nocols = 7 'InputBox("Enter Number of Columns Desired") For i = 1 To rng.Row Step nocols Cells(J, "A").Resize(1, nocols).Value = _ ...

External Data in Pivot Tables
I have a series of files in Excel that I would like to summarize with Pivot Table. The files are too big -- too many lines -- to combine an run the table. Is there a way to link multiple external files into on workbook and run a table? I've explored the external data option, bu I can only get one file with it. Thanks Staci -- Message posted from http://www.ExcelForum.com You could use MS Query to import the data (Data>Import External Data> New Database Query) and use an SQL UNION statement to combine the data into one table. SPenney wrote: > I have a series of files in...

How to delete most of the "custom sort list"
I didnt like what Excel imported now I have a lot of junk that will take a very very long time to remove from the custom list. Is there a way to delet them? I tried shift control and selecting the ones but that didnt work. Thanks Do you want to delete all of them? If yes, you could run a macro: Option Explicit Sub testme() Dim iCtr As Long For iCtr = Application.CustomListCount To 5 Step -1 Application.DeleteCustomList iCtr Next iCtr End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/...

Problem with Import Data path
In Excel 2002 (Windows XP Pro OS), is there a way to have the Import External Data path default to the last folder visited (the way it did in Excel 97), rather than going to "My Data Sources" each time? All my text data files are organized deep under several layers of folders withing my general "Data" folder, and as it stands now, I need to navigate through the same set of multiple folders every time I want to import four or five seperate data files which all reside in the same folder. I don't see a way to change Excel's behavior. You could navigate to your...

oldcompanyweb won't work
I am in the process of migrating from SBS 2003 to SBS 2008. Everything seems to have worked well until I hit a wall on the Sharepoint migration. I copied the "old" companywed site to the target server, per the instructions, mounted the STS_SERVERname_1 database and created the web application named "oldcompanyweb". Then I got to the step where I have to set the site collection administrators. When the site collection administrators dialog opens and I try to select site collection "oldcompanyweb" the selection pane says there are no items to show in this ...

Transpose question
I have a MASTER summary worksheet with this layout 07-Sep 14-Sep 21-Sep 28-Sep 05 - Oct Total No of late deliveries 36 40 20 10 9 115 No of Drivers 18 32 11 11 4 76 getting its info from WEEKLY worksheets eg 03-Sep 04-Sep 05-Sep 06-Sep 07-Sep Total No of late deliveries 7 8 7 7 7 36 No of Drivers 4 3 1 6 4 18 I would like the master layout to be like : No of late deliveries No of Drivers 07-Sep 36 18 14-Sep 40 32 21-Sep 20 11 28-Sep 10 11 05-Oct 9 4 Total 115 76 But as the values in the MASTER worksheet ...

Excel 2002 Error: Data may have been lost
I support a number of users on Windows XP with MUI (multilingual user interface) and Office 2002 MUI. Mostly running in Japanese settings. All machines are identical as they are built from ghost image. I had one call when user could not open excel file. Excel file is in Japanese with lots of formatting. I logged in as local admin and got same error message: data may have been lost. Then all characters were corrupt. I logged into another PC, same propblem. Then I asked a number of collegues open same file and they didn't have any problems. They could open it on my PC as well. I ha...

remove guotes from cells containing commas
I have a column that contains commas. When saving as a tab delimited txt file, excel puts the contents in quotes. I would like to somehow have excel not put the quotes around the contents of that cell. I could do a search and replace after opening in a text editor, however that would remove quotes in columns that contain product descriptions such as an item described in "(inches) Take a look here: http://www.mcgimpsey.com/excel/textfiles.html#csvnoquotes In article <1127224181.284319.303490@g44g2000cwa.googlegroups.com>, "hwalders" <hwalders@hotmail.com>...

Extracting Mailbox data using ExMerge
Hi, I am having trouble using ExMerge with the recovery storage group. I have successfully restored a backup to the RSG and have run the exmerge util, only to be given an error msg and log file which don't provide any real info. Error msg is as follows: "An error was encountered when retrieving the list of mailboxes homed in the selected databases on server 'SERVER'. Please refer to the 'ExMerge.log' log file." Unfort the log file gives no further info, see following: [17:17:21] Logging Level: None [17:17:21] Reading settings from file 'C:\Program Fi...

Can't open hyperlink within Outlook message
Suddenly, without applying any updates to my PC or any Window Updates, I can no longer open any hyperlink withinin any email. Error screen appears "This operation has been cancelled due to restrictions in effect on this computer. Contact systems administrator". I am the systems admin and I have made no changes. I use Vista 64 bit. I have spent hours searching out help within Outlook, viewing Control Panel settings, etc. I'm stuck. See http://www.slipstick.com/problems/link_restrict.htm for the latest information. -- Peter Please Reply to Newsgroup...

can I delete an email through system manager or other utility
I support a large network and occasionally spam will get through and cause problems with my veritas backup. I know which email caused the problem and who's mailbox it lives in. Is there a way I can delete from the server or with another utility without going through the users outlook. you can exmerge it out of the mailbox if you have the exact subject line...but I do not see how spam getting through could cause problems with your backups... "darren" <darren@yahoo.com> wrote in message news:%23jZ6hS%23nEHA.3908@TK2MSFTNGP09.phx.gbl... > I support a large network and ...