| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 53914 | 2005-01-31 02:27:00 | Excel Macro/VBA help needed | Tony (4941) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 319613 | 2005-01-31 02:27:00 | Is Parry there....? I have a work sheet that has column A with given names and column B with surnames. I want to end up with a column (could be A, B or a new column) with given name + space + surname. My usual method is to to record a macro and then try and tweak it, but I obviously don't know zip about Excel macros/VBA, as what I end up with is nothing like what I expected. I suspect this is all dead easy, but if anyone can point me in the right direction I would be very grateful. TIA, |
Tony (4941) | ||
| 319614 | 2005-01-31 04:18:00 | The formula is =A1&B1 You will need to ensure that the name that comes first has a space at the end of the text. |
wotz (335) | ||
| 319615 | 2005-01-31 04:26:00 | ... ensure that the name that comes first has a space at the end of the text. That seems a bit restrictive. Surely there's a way to add a space in the middle. ;) |
Graham L (2) | ||
| 319616 | 2005-01-31 04:36:00 | That seems a bit restrictive . Surely there's a way to add a space in the middle . ;) =A1 & " " & B1 does it . I knew it had to be easy . I was trying to be too clever and using macros and VBA when the easy answer was there all the time! What comes from hardly ever using Excel, I guess . Thanks everyone . :thumbs: Edit: Next question: if I put the formula in Column C, I then have col A = firstname, col B = surname, col C = combination . Of course if I now delete cols A & B, col C formulae are no longer valid! So the next question is: how can I convert the formula in col C to absolute values so I can then delete cols A & B? I'm sure I've managed to do this in the past, but it's all gone vague . . . |
Tony (4941) | ||
| 319617 | 2005-01-31 05:40:00 | Select the cells in column C, go Ctrl+C and then from the edit menu, Paste Special and then Values . Before pasting, either move to a new cell or stay in Column C to overwrite the formulas . Cheers . RogerRamjet |
RogerRamjet (7055) | ||
| 319618 | 2005-01-31 06:49:00 | Hi Tony, I see you have been in capable hands and your all sussed . :thumbs: If you later need to split the combined first name/surname into separate first names and surnames again you can use formulas to do this as well . The following examples presume the target cell is A1 . To get First name . . . =LEFT(A1,FIND(" ",A1,1)-1) To get Surname . . . =MID(A1,FIND(" ",A1,1)+1,LEN(A1)-FIND(" ",A1,1)) |
Parry (5696) | ||
| 319619 | 2005-01-31 10:24:00 | Thanks Roger and Parry - all sussed now . Parry, I'm embarrassed I didn't work it all out for myself . :blush: You'd never think I once created an entire accounting system for a small group I was involved in, including producing the annual accounts - just using Excel . It would all have been second nature back then - but as time goes on you forget . . . (puts on carpet slippers and creeps off to bed) |
Tony (4941) | ||
| 319620 | 2005-01-31 10:36:00 | =CONCATENATE(A1," ",B1) where a1 is the first name and b1 is the last name and the formula could be entered in c1 and copied down the column this will allow you to delete entries in a1 and b1 and with no errors showing in c1 only what appears to be a blank cell |
beama (111) | ||
| 319621 | 2005-01-31 10:46:00 | Oh goody. Some people that know excel. Can I hijack your thread Tony? I'm sure you wont mind. I have a couple of small things at work I have been trying to do but we dont have too many staff clued up on excel. 1. One of my workbooks has about 40 worksheets in it. Can I, or how do I, sort the worksheet tabs on the bottom alphabetically? 2. Is it possible to make the dropdown box wider? Some of the "insert, names" are quite long, and the dropdown box is quite narrow. Thanks for any good info. |
*Sparky* (311) | ||
| 319622 | 2005-01-31 14:39:00 | 1.It is possible to change the order of worksheets by clicking on the tabs then "dragging and dropping". I don't know of any short method to sort alphabetically except perhaps a macro which would be overkill in this instance. 2. Your column isn't wide enough? Either "format\columnwidth" or hold your mouse cursor at the top of the column immediately to the right of the column letter. When the cursor becomes a small black cross with a double ended arrows a quick double click will resize the column to automatically fit the widest entry. This might or might not make sense. It's late and I'm tired. Goodnight and good luck Capt Jimbo |
Capt Jimbo (17) | ||
| 1 2 3 | |||||