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:
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.
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.
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.
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)
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.
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 <Lastname, Firstname> into <Firstname Lastname>.
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
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.