# Excel 2010 macro - editing text within a cell **URL:** \ **Category:** Factual Questions\ **Created:** [October 2, 2013, 5:55pm UTC](https://boards.straightdope.com/t/excel-2010-macro-editing-text-within-a-cell/670198 "2013-10-02T17:55:12Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Boyo\_Jim](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@Boyo\_Jim](https://boards.straightdope.com/u/Boyo_Jim)\ **Post date:** [October 2, 2013, 5:55pm UTC](https://boards.straightdope.com/t/excel-2010-macro-editing-text-within-a-cell/670198/1 "2013-10-02T17:55:12Z")
I cannot figure out how to write a macro to move the cursor around within a single cell and move/cut/paste the text within the cell. The cell contains “Doe, John”. I want to change it to “John Doe”, and then move down a cell to where I can repeat the macro on another name if I want. The problem is that even though I can perform the operation while recording, the macro ends up pasting the name from the first cell to all the later cells. The secquence I record, using relative cell referenceing, is: [F2] - (edit the cell – which puts the cursor withing the cell after the last character) [Ctrl]+[Shift]+[left arrow key] – (selects the last word – “John”) [Ctrl]+[C] – Copies “John” [Delete] [Delete] [Delete] – which deletes “john”, the blank space, and the comma [Home] – which moves the cursor to the left end of the cell [Paste] – Which pastes “John” at the beginning [Space] – Which adds a Space between John and Doe [Enter] – which ends the edit and moves the cursor to the next cell and then “Stop recording” Somehow, this whole process ends up being written in the macro as: ``` ActiveCell.FormulaR1C1 = "John Doe" ActiveCell.Offset(1, 0).Range("A1").Select ``` End Sub Where did all my commands go? Hep me please! ---
**Author:** ![Disgscen](https://avatars.discourse-cdn.com/v4/letter/d/f05b48/32.png) [@Disgscen](https://boards.straightdope.com/u/Disgscen)\ **Post date:** [October 2, 2013, 8:02pm UTC](https://boards.straightdope.com/t/excel-2010-macro-editing-text-within-a-cell/670198/2 "2013-10-02T20:02:21Z")
VBA doesn’t work within cells the way you’re thinking. And while you could do this with VBA, an Excel formula is sufficient. If you had a column of names, with “Doe, John” in A1, put the following in B1: =RIGHT(A1,LEN(A1)-FIND(", “,A1,1)-1) & " " & LEFT(A1,FIND(”, ",A1,1)-1) Looks messy, but it’s just using the length of the contents of A1, as well as the position of the comma and space, to reformat your name in B1. Once you’ve got B1 working, you can expand down for all other names on the list. Then if you wish copy column B, and do a Paste Special of just the value to column A, then delete B. This leaves a single column formatted as you want. ---
**Author:** ![Tim\_T-Bonham.net](https://avatars.discourse-cdn.com/v4/letter/t/46a35a/32.png) [@Tim\_T-Bonham.net](https://boards.straightdope.com/u/Tim_T-Bonham.net)\ **Post date:** [October 2, 2013, 8:58pm UTC](https://boards.straightdope.com/t/excel-2010-macro-editing-text-within-a-cell/670198/3 "2013-10-02T20:58:28Z")
Proper design would have put “John” in one cell and “Doe” in another. And it’s easy to fix that == judt use text functions to split the name. ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [October 2, 2013, 9:06pm UTC](https://boards.straightdope.com/t/excel-2010-macro-editing-text-within-a-cell/670198/4 "2013-10-02T21:06:52Z")
How about using the CONCATENATE function? If “Doe,” is in column A, and “John” is in column B, then: =CONCATENATE(A1,B1). Then search and replace the comma with a space. ---
**Author:** ![Trinopus](https://avatars.discourse-cdn.com/v4/letter/t/2bfe46/32.png) [@Trinopus](https://boards.straightdope.com/u/Trinopus)\ **Post date:** [October 3, 2013, 12:11am UTC](https://boards.straightdope.com/t/excel-2010-macro-editing-text-within-a-cell/670198/5 "2013-10-03T00:11:53Z")
> [@t-bonham@scc.net](#): > > Proper design would have put “John” in one cell and “Doe” in another. > > And it’s easy to fix that == judt use text functions to split the name. And be sure to select both columns when sorting! I’ve actually sat at work and watched people sort only the “Last Name” column, thus losing all relevance to the first names. Oopsie! MS Office has so many wonderful little traps for the unwary. ---
**Author:** ![Boyo\_Jim](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@Boyo\_Jim](https://boards.straightdope.com/u/Boyo_Jim)\ **Post date:** [October 3, 2013, 2:24pm UTC](https://boards.straightdope.com/t/excel-2010-macro-editing-text-within-a-cell/670198/6 "2013-10-03T14:24:03Z")
> [@Disgscen](#): > > VBA doesn’t work within cells the way you’re thinking. And while you could do this with VBA, an Excel formula is sufficient. > > If you had a column of names, with “Doe, John” in A1, put the following in B1: > =RIGHT(A1,LEN(A1)-FIND(", “,A1,1)-1) & " " & LEFT(A1,FIND(”, ",A1,1)-1) > > Looks messy, but it’s just using the length of the contents of A1, as well as the position of the comma and space, to reformat your name in B1. > Once you’ve got B1 working, you can expand down for all other names on the list. > > Then if you wish copy column B, and do a Paste Special of just the value to column A, then delete B. This leaves a single column formatted as you want. Thank you! This works erfectly. ---
**Author:** ![Boyo\_Jim](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@Boyo\_Jim](https://boards.straightdope.com/u/Boyo_Jim)\ **Post date:** [October 3, 2013, 2:25pm UTC](https://boards.straightdope.com/t/excel-2010-macro-editing-text-within-a-cell/670198/7 "2013-10-03T14:25:07Z")
> [@t-bonham@scc.net](#): > > Proper design would have put “John” in one cell and “Doe” in another. … I will speak sharply to whoever wrote the spreadsheet, should I ever find them. ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [October 3, 2013, 7:33pm UTC](https://boards.straightdope.com/t/excel-2010-macro-editing-text-within-a-cell/670198/8 "2013-10-03T19:33:40Z")
> [@Saintly\_Loser](#): > > How about using the CONCATENATE function? If “Doe,” is in column A, and “John” is in column B, then: =CONCATENATE(A1,B1). Then search and replace the comma with a space. That should be =CONCATENATE(B1,A1). Sorry about that. ---
**Author:** ![Tim\_T-Bonham.net](https://avatars.discourse-cdn.com/v4/letter/t/46a35a/32.png) [@Tim\_T-Bonham.net](https://boards.straightdope.com/u/Tim_T-Bonham.net)\ **Post date:** [October 4, 2013, 1:22am UTC](https://boards.straightdope.com/t/excel-2010-macro-editing-text-within-a-cell/670198/9 "2013-10-04T01:22:15Z")
> [@Boyo\_Jim](#): > > I will speak sharply to whoever wrote the spreadsheet, should I ever find them. Just fix it! With the name in cell A1, add cells B1 for first name & C1 for last name, with formulas as follows: in B1: =LEFT(A1,(SEARCH(", “,A1)-1)) in C1: =MID(A1,(SEARCH(”, ",A1)+1),40) ---
**Author:** ![Disgscen](https://avatars.discourse-cdn.com/v4/letter/d/f05b48/32.png) [@Disgscen](https://boards.straightdope.com/u/Disgscen)\ **Post date:** [October 4, 2013, 2:45pm UTC](https://boards.straightdope.com/t/excel-2010-macro-editing-text-within-a-cell/670198/10 "2013-10-04T14:45:11Z")
> [@t-bonham@scc.net](#): > > Just fix it! You don’t know it’s broken. Excel isn’t a database, and unlike a table in a database, Excel sheets are often printed up, or read as text, or discussed among groups of people. It isn’t usually one element of the data–it’s a representation of all the data, and thus readability is usually extremely important. Sure, if you’re using an Excel sheet as just a simple database, FirstName LastName in individual cells is a good idea. But this thread demonstrated a situation in which a spreadsheet can be more useful than the equivalent database. The OP inherited a list of names. Was that original list bad design? For all we know, it could be perfectly designed text, as output from a phone book. Good or bad, that’s what he had, and Excel is a great tool to convert that into exactly what the user needs. That’s what defines good design. ---
**Author:** ![Disgscen](https://avatars.discourse-cdn.com/v4/letter/d/f05b48/32.png) [@Disgscen](https://boards.straightdope.com/u/Disgscen)\ **Post date:** [October 4, 2013, 4:40pm UTC](https://boards.straightdope.com/t/excel-2010-macro-editing-text-within-a-cell/670198/11 "2013-10-04T16:40:11Z")
I assumed for my answer @2 that the OP wasn’t used to VBA, and that an Excel formula would be preferred. For those situations in which VBA is better, I’m answering the original question again, but more directly. The macro recorder doesn’t really notice anything you do within a cell–it just records the final value when you hit Enter. This can be a good thing, because a lot of what humans do–selecting, deleting, copying–are just steps to get the desired result. Try to forget the steps, deal with the contents of the cell as a string, and focus on the desired results. Though it looks a bit different, the following does the same operations to any selected cells that I used in my formula-based answer @2, to change \ into \. Sub FixSelectedNames() For Each oName In Selection oName.Value = Right(oName, (Len(oName) - InStr(1, oName, ", ") - 1)) \_ & " " & Left(oName, InStr(1, oName, ", ") - 1) Next oName End Sub ---
**Author:** ![newstudent](https://avatars.discourse-cdn.com/v4/letter/n/c77e96/32.png) [@newstudent](https://boards.straightdope.com/u/newstudent)\ **Post date:** [September 11, 2014, 2:16am UTC](https://boards.straightdope.com/t/excel-2010-macro-editing-text-within-a-cell/670198/12 "2014-09-11T02:16:20Z")
Hi, I need help too , I require a macro or formula to repeat a process. my question I have a text in a cell , example “RRR-11-11 YYYYYYYYYYYYYYYYY” can some one show me the formula or function to delete all the YYYYYYYYY and remain the front portion RRR-11-11 Appreciate some one can offer answer. Thank you. ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [September 11, 2014, 3:40am UTC](https://boards.straightdope.com/t/excel-2010-macro-editing-text-within-a-cell/670198/13 "2014-09-11T03:40:09Z")
> [@newstudent](#): > > Hi, I need help too , I require a macro or formula to repeat a process. > my question > I have a text in a cell , example “RRR-11-11 YYYYYYYYYYYYYYYYY” > can some one show me the formula or function to delete all the YYYYYYYYY and remain the front portion RRR-11-11 > Appreciate some one can offer answer. Thank you. If that is exactly how the data is formatted (with that space in the middle), just do a find/replace: Find: " \*" (space asterisk) Replace: “” (blank) It’ll delete everything after the space