# Reversing the Order of Words in Columns

1. ## Reversing the Order of Words in Columns

Hi,

I was wondering if anyone would be able to help me. I use is Microsoft Excel Starter 2010 (Microsoft Office version 14.0).

The only column I am using is "Column A" in my excel document. It is filled with approximately 20,000 names.

The names look like this:

F Bloggs
B Jones
S Brown
F Smith

What I want to do is reverse the order so they look like this:

Bloggs F
Jones B
Brown S
Smith F

Any help would be most greatly appreciated.

Thanks and kind regards,

Paul.

2. ## Re: Reversing the Order of Words in Columns

Enter formula in B1 and copy down
Formula:
`Please Login or Register  to view this content.`

 v A B 1 F Bloggs Bloggs F 2 B Jones Jones B 3 S Brown Brown S 4 F Smith Smith F

3. ## Re: Reversing the Order of Words in Columns

Here's another one...

Data Range
 A B 1 F Bloggs Bloggs F 2 B Jones Jones B 3 S Brown Brown S 4 F Smith Smith F

This formula entered in B1 and copied down:

=MID(A1,FIND(" ",A1)+1,20)&" "&LEFT(A1)

4. ## Re: Reversing the Order of Words in Columns

You could use something like this
Formula:
`Please Login or Register  to view this content.`

If by chance there is a name instead of an initial this version will handle both single given names and Single initials
Formula:
`Please Login or Register  to view this content.`

Capture.JPG

5. ## Re: Reversing the Order of Words in Columns

Thank you very much to all three of you who replied. All three solutions worked so thanks very much indeed.

Even though all three worked, I came across a couple of minor problems so I would like to ask two additional questions that would help me further.

(1) I tried arranging the information which is now in Column B into alphabetical order (clicking on Sort & Filter, Sort A to Z) but it stayed in the same order.

(2) A few hundred of the names consisted just of a single name (so the order didn't need rearranging). For all these names, it just says #VALUE! in Column B.

Like before, any information regarding these queries would be greatly appreciated.

Regards, Paul.

6. ## Re: Reversing the Order of Words in Columns

The reason that the order doesn't change is that the formulas always refer back to the original list. If you want alphabetic order you could simply copy and paste values the results of the formula used in place to remove the formulae or copy to another location and simply sort into order.

7. ## Re: Reversing the Order of Words in Columns

Originally Posted by PaulBollard
A few hundred of the names consisted just of a single name (so the order didn't need rearranging). For all these names, it just says #VALUE! in Column B.
Try this one...

Data Range
 A B 1 F Bloggs Bloggs F 2 B Jones Jones B 3 S Brown Brown S 4 F Smith Smith F 5 Kruzic Kruzic 6 P Balik Balik P 7 Tronzo Tronzo

This formula entered in B1 and copied down:

=IFERROR(MID(A1,FIND(" ",A1)+1,20)&" "&LEFT(A1),A1)

8. ## Re: Reversing the Order of Words in Columns

The reason that the order doesn't change is that the formulas always refer back to the original list. If you want alphabetic order you could simply copy and paste values the results of the formula used in place to remove the formulae or copy to another location and simply sort into order.
This will return single names from the list. Enter in B2 and fill down
=IF(A2="","",IFERROR(TRIM(MID(A2,FIND(" ",A2),25)&" "&LEFT(A2,FIND(" ",A2))),A2))
This however will get the list into alphabetic order for you
Enter this in C2 and fill down
Formula:
`Please Login or Register  to view this content.`

Enter this in D2 and fill down
Formula:
`Please Login or Register  to view this content.`

Data Range
 A B C D 1 Original Reversed Order Alphabetic order 2 F Bloggs Bloggs F 3 Able 3 Bob Jones Jones Bob 1 Bloggs F 4 S Brown Brown S 2 Brown S 5 Fred Smith Smith Fred 0 Jones Bob 6 Able Able 4 Smith Fred 7 8 =COUNTIF(\$B\$2:\$B\$20,">"&B2) 9 =IFERROR(INDEX(\$B\$2:\$B\$20,MATCH((ROWS(\$D\$2:D2)-1)*-1+MAX(\$C\$2:\$C\$20),\$C\$2:\$C\$20,0)),"")

9. ## Re: Reversing the Order of Words in Columns

Thanks very much to the two of you who replied to my latest post.

I no longer have the problem with single names showing up as #VALUE! which is great!

However, having tried the two formulas you suggested newdoverman for Columns C and D, there are still problems.

(1) Firstly, the list in column D consists of only 8 names.
(2) The 8 names listed are the top 8 different names from my list in column B, and only those names have been rearranged into alphabetical order in Column D.
(3) A lot of the names in columns A and B are duplicate names and it's only listed these once in column D.

Also, I have another excel document that looks something like this:

Column A...................Column B
F Bloggs.....................Red hair
Bob Jones...................Yellow Tie
S Brown.....................Black briefcase
Fred Smith..................Blue Eyes

For this particular excel document, how could I do exactly the same thing (i.e. reversing the names in column A and arranging the new list in alphabetical order) yet making sure what is written in Column B appears alongside the correct name?

Any help would be gratefully received and much appreciated.

Paul.

10. ## Re: Reversing the Order of Words in Columns

Point 1) There never were 8 names in the example list. Only 5 names were given.

Point 2) I do not understand this point at all.

Point 3) There was no mention of duplicated names in column A so no provision was made for that. Column B is derived from column A so if there are duplicates in column A they will also appear in column B.

Here is a guess at what you want...added columns and all. Column A has duplicated names, Column B has Other Data for the names, the other columns are basically the same as before only taking into account the added data and the length of the columns.

11. ## Re: Reversing the Order of Words in Columns

This file is more efficient, I believe.

12. ## Re: Reversing the Order of Words in Columns

I'm probably going to sound quite stupid now but I'm really struggling to open or save the file. I have Windows 10.

I click on the file and something comes up saying: Do you want to open or save attachment.php (9.78KB) from excelforum.com?

So I click on 'Open'. I think the first time I tried to open it, it asked me what program I wanted to open it in (and didn't have a clue) so I took a plunge and chose Internet Explorer. Now everytime I try to open the file, it opens it (or tries to open it) using Internet Explorer. I'm not sure how to reverse this - or if I was able to reverse this, what program to open it in instead.

Anyway, upon clicking on 'Open', a message comes up saying: What do you want to do with attachment.php? I click on "Open".

Obviously I'm doing something fundamentally wrong and probably sound pretty stupid. Do you know what it is I am doing wrong?

13. ## Re: Reversing the Order of Words in Columns

Originally Posted by PaulBollard
I click on the file and something comes up saying: Do you want to open or save attachment.php (9.78KB) from excelforum.com?
That's some kind of bug with the forum software. It's been around for several days now.

Choose Save, then choose Save As...

Save it with an Excel file name/extension. Filename.xlsx

14. ## Re: Reversing the Order of Words in Columns

Thanks very much for that advice Tony. I managed to open the documents straight away after that.

Thanks for the two attachments newdoverman,

I'm still having problems achieving my objectives. I think it's because I've explained myself very badly so I'll try to explain things a bit better......possibly from the beginning.

I have a excel document filled with 20,000+ names. They consists of: (i) just a single name, (ii) initialled names, (iii) two or more names, (iv) duplicates.

I copied and pasted these 20,000+ names into Column A of your List2.xlsx excel document and only the top 8 different names appeared in Column D.

I think this is perhaps the part that I explained very badly. Whereas you thought I was referring to the list of 5 names that were used as an example (F Bloggs, Bob Jones, S Brown, Fred Smith, Able) I was referring to the 20,000+ names that I copied and pasted from another excel document.

What is happening is: (1) only the names that appear on rows 2-20 are appearing in Column D of the List2.xlsx document.

That's why I mentioned before that only 8 different names appeared in Column D. It's because only 8 different names appeared on rows 2-20 out of my list of 20,000+.

(2) The duplicated names that appear on rows 2-20 are only appearing in Column D once. I would like all duplicated names to appear in Column D as many times as they appear in Column A.

Sorry for any confusion that I caused. I should have made it clear that I want to keep duplicates.

Hopefully, I've done a better job of explaining myself this time around.

Regards, Paul.

15. ## Re: Reversing the Order of Words in Columns

Did you fill the formulae down the columns to accommodate all of your data? Unless you did that, you will get only partial results. The formulae as written will accommodate 25000 rows. I didn't copy the formulae down very far...just enough for you to see that they work. You will have to fill the formulae down if you haven't already.

Page 1 of 2 1 2 Last