Is there a way to reorder the contents of cells in Excel? Specifically: a number of cells contain a city name followed by ‘CITY OF’; for example, ‘ANYBURGH CITY OF’. I’d like to change the order to 'CITY OF [cityname], including a space after ‘OF’, without having to cut-and-paste individually.
Note: I am completely unfamiliar with macros, so any such suggestions will need to be accompanied by step-by-step instructions.
Yes. Assuming the relevant data is in cell A1, enter in cell A2 (or wherever):
=“CITY OF” & LEFT(A1, LEN(A1) - LEN(" CITY OF")
nm
Well you wouldn’t have to use a macro.
There are several ways to do this, here’s one
Highlight all the cells that have the “City of” that you want changed
Then hit “Ctrl+H”
Then go to the replace tab
Then where it says “Find what”
Type in City Of
Leave replace with blank
Then hit replace all.
This will replace “City of” with nothing.
This leaves you with all the city names only
Close out of Find/Replace
Then for step 2:
In the column next to all the city names type the words City of in one cell and copy that for all the cities on your list.
So now you should have something like
Column A -> Column B
Minneapolis -> City of
Chicago -> City of
Detroit -> City of
Then in colum C you use a function called “Concatenate”
So in this if Minneapolis is in cell A1 and City of is in cell B1 you would go to cell C1 and hit the Fx button
Then go to the Concatenate funtion (found in text) and follow the prompts
Basically it’ll read (in column C1):
=CONCATENATE(B3,A3)
But now you’re saying, “Mark I want a SPACE”
So you have to alter this a bit
It should read
=CONCATENATE(B3, " ",A3)
See how the little " " put a space.
Then you copy that formula all the way down your list
Now for the final step.
This is fine for what it is but the cells in column three are pulling from other sources so you don’t want that.
So what you do is highlight all of column C which contains the correct names
City of Chicago
City of Minneapolis
City of Detroit
Then you copy that column and go to a blank column (in this case it’d be column D)
Then you RIGHT CLICK and PASTE SPECIAL -> Values only
This way your cells in column D are correct and aren’t pulling from anything.
You could write a macro and there are lots of ways of doing it, but this is one that goes step by step and helps you learn the process a bit
OK, here’s what I did:
The names are in column B. I inserted a new column ©, found a cell with ’ CITY OF’, placed a character in column C, and sorted to bring that record to the top (row 2, after the header record). I copied the code and changed it so:
=“CITY OF” & LEFT(B2, LEN(B2) - LEN(" CITY OF")
I thought that this would yield RENTON CITY OF in B2 and CITY OF RENTON in C2. Instead, I have RENTON CITY OF in B1, and =“CITY OF” & LEFT(B2, LEN(B2) - LEN(" CITY OF") in C2.
You’ve got a close bracket missing at the end.
Check the formatting of column C. If the cells are formatted as “text” then formulas will not work and will just appear as text strings. If this is the problem, change the format to “General”. Then click on C2, hit F2, Enter, and the formula should work.
True, but Excel would give an error in that case. If there is no error the cell is probably formatted as text.
That did it!
Thanks!
(I’ve already gone through and changed the 96 records manually. But I’ll email this to my work email so I have it for later.)
A follow-on question: A lot of records have [space]E at the end of them. Can similar code be used to eliminate them?
This file has 20,000 records, and the initial cleanup is time consuming. Once the initial cleanup is done, I’ll write a comparison program (Easytrieve) to fix previously-received accounts and leave only new accounts to be cleaned. But I do get large accounts with extraneous characters that do not lend themselves to Find & Replace from time to time, and if a formula can do it it would be most helpful.
Again assuming the value in question is in A1, enter in A2:
=IF(RIGHT(A1,2)=" E",LEFT(A1,LEN(A1)-2),A1)
Outstanding!
Thanks!
If you have excess space then just use the Replace function. You can start with any amount of blank spaces and replace them with nothing. When you’ve pared it down to small amounts then repeat the replacement of double blank spaces on the assumption that you don’t have double spaces between anything. Don’t replace single spaces with nothing as that would eliminate all spacing.
That’s the problem: No spaces after the E.
It doesn’t matter where the spaces are you’re trying to eliminate. Replace will find them and replace them with nothing. No formula needed. You just have to avoid replacing a single space.
If I just replace E with nothing, then UNIVERSITY OF OREGON would come out UNIVRSITY OF ORGON.
You said you wanted to get rid of blank spaces. You can replace anything with anything (or nothing).
Actually, I was looking to get rid of [space]E at the end of the cell.
As for my example, I was in a hurry. Since I’m trying to get rid of [space]E, just getting rid of [space]E would not affect U of O. But if I had something like FAST EDDIE’S EQUIPMENT it would come out as FASTDDIE’SQUIPMENT. No need to get rid of blank spaces at the end of a record, but I routinely change multiple spaces to single spaces because of limited field width in the final file.
In any case, fighting ignorant’s formula got rid of the [space]Es at the end of the cells. 
I’d rather teach you to fish. There are a few functions you should know. Right, Left, Mid, Len, If, and Concatenate. Looking those up in the help files should go a long way toward helping you figure it out the next time around. Remember the trick that’s already been posted here: If the strings aren’t the same length in all cells you can do a Len(Len-right) trick to get just the left characters. The opposite is true, obviously, for the keeping the right half.
I was thinking of that earlier, that I’d like to know the basics of that kind of formula writing.
But can you really teach me to fish? Lots of salmon in the local waters…