Menu
But you want first name and last name in separate columns. Select the column that the names are in, click on the Data tab on the ribbon, and click the This tells Excel that anything separated by spaces should be split into different columns. You will see vertical lines appear in the preview of your data to.
Hi all. Is there a way within excel itself or a macro that can be written to reverse a first name, last name to last name, first name other than text to column and then formula to concetenate? example: Smith, Joe to Joe, Smith Thanks Subscribe for Weekly Excel Tips and TricksHelpful tutorials delivered to your email!Similar Topics
Col A contains full name. Might be Bob Smith and might be Robert Smith Jr. or Carol De La Cruz. Regardless, I need to put anything after the first space first, with a comma, then the first name last. So Bob Smith would be Smith, Bob and Carol De La Cruz would be De La Cruz, Carol and Robert Smith Jr. would be Smith Jr., Robert. So far I have this: =MID(A2&' '&A2,FIND(' ',A2)+1,LEN(A2)) It works, but I have no comma and space before the first name. What am I missing? Hi, I am looking for a method/formula that will reverse multiple text entries from 'abcde' to 'edcba'. The entries are composed of several words that need to be reversed ie from 'ab cde fg' to 'gf edc ba' Thanks
Hi all, I've had a long search through your pages to see if this question has been answered before but having browsed through about 50 pages worth of threads I couldn't see anything, but if I am repeating prior information I do apologise. I've written a macro that is relatively simple. It just takes some information in one format, rearranges it, adds some formatting and performs some calculations. Nothing incredibly fancy but it works fine on my computer. Now, I need to share this macro with some other people, so basically I've just sent that excel file on to the people that need to use it. Should be fine and in most cases it is, however there is one user who although they can open the file, can't seem to get the macro to run properly. It seems to get a small way through the macro but then stop with no error messages or any sign that it hasn't completed properly. I have checked Macro Security level and that is the same as mine, Tools - Add-Ins is the same, In Visual Basic, Tools - References is the same as mine. It is the same Operating system and the same version of Excel. I have even signed into this person's computer as myself (it's a big company network thing) and tried to run the macro and it works fine, so there is nothing wrong with the hardware. I've googled and searched and tried everything I can think of but I'm no closer to solving this problem, so if anyone has read through this wall of text and can come up with a possible solution, that would be greatly appreciated to save me from tearing ALL my hair out! Thanks very much for your time.
Hi, I am entering lots of family history data into a spreadsheet. At the simplest I have columns (in cells A1 B1 C1) the headings, Surname, Forename, Year. Right now, I enter in cells A2 B2 and C2 say: Smith <Tab> John <Tab> 1555 <Enter, move mouse to the A column in the next row down). What I want to happen is when I have entered the last data in a row and pressed <Enter> I move automatically to the A-column in the next row down. Is this possible? Regards and a Merry Christmas to all Wibs I have a name convention in one cell that has the last name first serperated by a comma with the first name last. How can I reverse this to show the first name first and the last name last in one cell? Hello, I'm a bit of a newbie with Excel, but... I have several cells with data that contain both letters and numbers (4H, 8V, 4FH, etc.) What I want to do is remove only the text characters from these cells, and add the remaining number values together among a series of cells with this data type. If your answer involves using a macro or VB, please provide a link on how to use the formula. I've never used a macro or VB. Thanks. Hi, I would like to send SMS from a VBA macro to my mobile phone. Do anyone know how to do this? I am ready to pay a cost per SMS if necessary. (I asked the same question at another Excel forum without getting any reply.)
Thought I'd start this topic since there seem to be a number of topics where the answer seems to be to use one of the above rather than other. Thought I'd kick off with my 2 cents' worth. I have a userform with frames containing textboxes. The user enters a currency value and once they leave the control, then a protected textbox next to it shows the corresponding value in SEK. I started off using the exit event but ran into 2 problems. If you tabbed out of the last textbox in the frame, the exit event never kicked in (this is documented in other topics but took some time to find). This resulted in me using the exit event for all except the last textbox in the frame that used afterupdate insteadI then discovered that the exit events didn't kick in if, instead of tabbing out of the field, I deliberately placed focus in a control elsewhere on the form. Changing the event from exit to afterupdate corrected this.My question then is ... could you guys document in this topic when you would/must use the exit rather than the afterupdate event (or vice-versa). Thanks
I have two columns in my spreadsheet. Column A has no blank fields, Column B has some blank fields. I would like to show the text from Column A in the same row of Column B only when Column B is blank. So, basically I'm after: If column B has text, do nothing. If column B is blank, then list text from Column A. Right now I've created Column C with this formula =IF(ISBLANK(B1), A1. That's working to get the text from A1 when B1 is blank. What can I do/add to get it pull the text from B1 if there is text there? Or...is there a better approach altogether?
Helllo, I have written a macro and at the end it displays a message 'The macro has finished'. I would like this message box to disappear after 2 seconds automatically so that users don't have to press the OK button all the time. can this happen ? thanks andy
I have a large database with names, addresses etc. When I try to make changes to the email address, like change the font color or change on letter, Excel tries to email the address. If I click in the formula bar to make the changes, it automatically changes it back to all blue text and underlined. I have tried to format all of the cells as 'Text' to show as entered, but it doesnt work. PLEASE HELP Hello All, I need a macro enabled file to send HTMLbody email with pre populated text to list of receipents. Any Idea How can i do? Many thanks Ben
Hi guys, would really appreciate anyones help with this. I have a column full of text-formatted fractions... 4/5 6/4 3/1 2/5 4/5 etc, etc.....the column is very long!! I need to convert these to actual fractions...ones I can add to equations, allowing me to multiply and add. If I highlight the column and goto 'format Cells' and change to fraction, the values still stay in the left of the cell.....its only when I manually click on each individual cell, then tick the little green tick, does the cell validate, and the value in the cell moves to the right (and is therefore a proper value).....problem is I have to keep doing this for each individual cell.....and I have 70000+ cells in this column! Any suggestions on how I could speed this up? thanks I have an Excel workbook that was created by a former coworker. It includes a macro that, among other things, displays a message box about the 2008 file. The macro runs as soon as the file is opened. I'd like to access that macro to correct the date to 2011 and see what else, if anything, it's doing for me (it doesn't appear to do much). I can find references to creating macros to hide and unhide rows/columns and I found ways to delete all macros in a workbook, but I cannot find anything about unhiding a macro without knowing its name. Does anyone know of a way to unhide this macro? Thanks!
I use a excel file through the course of the day and need to insert the current date in one column and the current time in the next column. I want to be able to just highlight the selected range of cells I need to insert into and hit a macro button and have the date and time inserted into just the cells I have highlighted. I'm not sure how to make this work with just the cells I've highlighted. Any help any one can give me would be greatly appreciated. Thanks! Mike I am trying to do a very simple copy and paste of a simple formula in Excel 2007 and nothing seems to be working. Excel will not let me copy and paste a formula and will only paste the value into to workbook. For instance, if I make a very simple spreadsheet such as: A1 type in 10, A2 type in =A1 (calculated A2 to be 10) B1 type in 5 And then click the copy on B1, and then click paste special on cell B2, the only options it gives me are text and unicode text and so no matter how I paste, cell b2 will always be populated with a value of 5. I cannot imagine a simpler copy and paste and no matter what I do I can't make this work. All cells are formatted as general. It seems that all copy and pasting of formulas in my excel has been disabled. If I open any spreadsheet on my computer, I can't copy and paste formulas, but do the exact same thing on the exact same spreadsheet on any other computer and it works no problem. Any help?
I am trying to get my macro to do this on open. Check if a1 is empty, if its empty run another macro (which is an input box to put something IN a1), but if A1 has something in it, DO NOTHING. I keep getting errors, saying I'm not formulating my if statements correctly. This is what I have: Code: Arcangelo from Italy asks: How can I write an Excel VBA macro to save the current Excel file with a filename derived from cell A1? This macro is amazingly straight-forward: Public Sub SaveAsA1() ThisFile = Range('A1').Value ActiveWorkbook.SaveAs Filename:=ThisFile End Sub I'm totally lost on this 'amazingly straight-forward' macro!!!! Could someone help if I tell you the SAVE location? It's T:COMMISSIONINGIJTTIMELOG projectStaff#1. I'm just not certain what value I'm replacing in the Macro above. Any help appreciated..
Is it possible to import a single text file into Excel, splitting the incoming data across multiple worksheets rather than a single worksheet? Each each row on the text file would be evaluated by the value in one of it's 'columns' and written to the appropriate worksheet. The file is '!' delimited and has 11 columns for each row. Currently, I import the file into one worksheet and cut/paste the rows manually into new worksheets/tabs. The files are very large, sometimes exceeding the 65,536 row limit, which I could avoid if the data was split out coming in. Any help anyone could provide would be WONDERFUL. Thanks! I have been using Excel (XP) to make a text chart for several months. Some of my text entries are rather lengthy. The past two weeks these lengthy entries are showing up as pound signs (#########) when I click off the cell. I know the text will fit in the cell, and the problem isn't solved by making the cell bigger or using a little bit less text. I have the cells formatted as 'text' and 'wrap to fit'. I have printed the pages and the printed version also has pound signs. I just want my text to show up!
Right now I have a sports league with 8 teams, I'd like to create a random 7-game schedule where each team plays the other team only once. Essentially this will be a 7-game round robin. However, I'd like to be able to use this for any number of teams and games. I'd like to do this in Excel, but I can't figure out how to have a randomly generating non-repeating macro with text values in cells. So right now I have a column of 8 values and need matrix of 7 columns by 8 rows next to it. I've found this thread that has one for numbers, but I can't figure out how to do it for text values... http://www.excelforum.com/excel-prog...m-numbers.html Thanks all! I am creating a data sheet to be completed by other users. I would like to format the text cells (name, etc) to have text entered as uppercase automatically although the user might use title or lower case. UPPER function cannot make cell look at itself and perform the function Excel 2003 Hi there, longtime user firsttime poster. Looking for some help as I am a non-expert with macros. Here's what I'm trying to do: We have to submit things to a certain regulatory body and we usually enter tasks in as soon as they come, do the submission, and then keep a record of that submission. So, I have a workbook with two sheets, one is 'TO DO', the other is 'ARCHIVE'. Both sheets have the same columns and everything. I am looking for a macro that will automatically cut a (row) from the TO DO sheet and paste it in into the ARCHIVE sheet once it is done, then delete the cut row from the TO DO list so it stays topped up. The trigger for archiving is the columns M and N which are titled 'Complete ?' and each has a validation drop down that says 'YES'. When both cells in columns M and N have the YES in them, I would like the macro to make the above mentioned actions. I ran a search on the forums and found something similar, but not quite what I was looking for. Any help?
I was wondering if there is a way of getting a macro to open the 'save as' dialogue box and then have it fill in the 'file name' field. I dont want it to actually make the save though as I want the user to have the chance to check that the save location is correct and the option to change the filename if required. Cheers.
Hello , Here is my issue: I have a list of SKUs in Excel and I need to add one same word to 500 cells that have pre-existing text in the cells. See example below: 1) List of SKU's in cells: 34 35 39 55 2) text that needs to be added IN FRONT of every number: DF So the result would be: DF34 DF35 DF39 etc. How do I do that in a formula and not manually? I found another similar thread and I understand how to do it from the instructions there but the same text goes on the back and the result is: 34DF but I need it to be like: DF34. I am using Office 2007 for mac. Thank you for the help, Chris |
If you have a list of names in Excel, with first and last names separated by a comma, you can use an Excel feature to split first and last names into separate columns.
Select the List
To start, select the cells that contain the names.
On the Ribbon in Excel 2007, click the Data tab, then click Text to Columns.
If you're using an earlier version of Excel, click the Data menu, then click Text to Columns.
![First name and last name generator First name and last name generator](/uploads/1/2/5/8/125825336/803756168.png)
The Convert Text To Columns Wizard
The Convert Text to Columns Wizard opens, and you can select the type of data that you want to convert.
Note: The wizard layout varies slightly in different versions of Excel, but the steps are the same.
- The list in this example uses a comma to separate the names, so select Delimited, then click Next.
- Next, add a check mark to the delimiter that's used in your text. You can select more than one, but in this example only Comma is required. We only want to split first and last names were the comma is inserted.
- Click Next, to go to Step 3.
- Select a destination cell, where you want the split text to start. The default setting is to overwrite the existing data, so if you want to keep it, select a different cell as the destination.
![2011 2011](http://support.apple.com/library/content/dam/edam/applecare/images/en_US/osx/el_capitan-mission_control.png)
- Click Finish, and the data is split into separate columns.
Other Methods For Splitting Data
This method is quick and easy, and perfect for a one time event, such as importing a list of names, and splitting it.
However, if the names will change frequently, you could use a formula to split the names, instead of the Text to Columns feature. Then the first and last name columns will automatically update, if the full name changes.
Chip Pearson has sample formulas for Extracting First and Last Names, and VBA code for splitting data.
________________________
________________________