# Question about excel



## ProWallGuy (Apr 7, 2007)

I have a spreadsheet with _*one column*_ holding names, like this:

Job Blow
John Smith
Harry Homeowner

So on, so, on.

Is there a quick way I can batch change the display of these to this format:

Blow, Joe
Smith, John
Homeowner, Harry


I realized after the fact that I should have separated first and last names to separate columns, but oh well...


----------



## ReNt A PaInTeR (Dec 28, 2008)

*Text to Column and Concatenate Formula*

Hey Tim,

Yes, there is absolutely a way you can do this. In Excel almost anything is possible. 


The first thing you want to do is move the text (last name) into a separate column and create a new column for the newly formatted name. To do this:

1) Select two columns (by clicking the column letters) right to the column that has your names

2) Right click your mouse and select "Insert"

3) Title your new columns




Now you need to move text to column (the last name of the person). To do this do the following:

1) Select the column that has the names by clicking on the column letter

2) Go to the "Data" tab

3) Click on "Text to Column" in the "Data Tools" group




The "Convert Text to Column" wizard will open.

1) Select "Delimited" if not already selected

2) Click "Next"

3) In the "Delimiters" box, un-check everything and ONLY select "Space"

4) Click "Next"

5) Finally, click "Finish"



This will move your last names or first names to the next row.



Now you are going to create a "CONCATENATE" formula in the second row you created to merge the names in the format you want them in.

1) In the next blank column, in row number 2, after the title you will type the following formula

*for "CellA" substitute with the cell title where your last name is for that first person, for "CellB", substitute with the cell title where your first name is for that first person. Copy formula to cell:


=CONCATENATE(CellA,","," ",CellB)


That's it! Hope this helps. You can also custom the formula to separate middle initials.

Take care now


----------



## [eric] (May 24, 2010)

*Method using a Formula*

Assume your Name column is A.

Insert a new column to the right and paste this formula.

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&LEFT(A1,FIND(" ",A1)-1)

You can drag the cell with the formula down to copy it to each cell in the new column.

Insert a new column.

Copy the column with the formula, Paste Special (choose values) into the new column.

If everything looks OK, delete your original column, and the one with the formula.

-- Eric


----------



## ProWallGuy (Apr 7, 2007)

Thanks, but even easier than all that, I just cut/pasted last name in front of first and added a comma. Only had about 175 to do, took maybe 20 minutes.


----------



## epretot (Dec 17, 2011)

ProWallGuy said:


> Thanks, but even easier than all that, I just cut/pasted last name in front of first and added a comma. Only had about 175 to do, took maybe 20 minutes.


Good call! The other two were just showing off anyway.:whistling2:


----------



## daArch (Mar 15, 2008)

thank you Rent,


I knew there was a way to easily get the first and last names delineated into separate columns, but didn't realize it was that simple. 

Excel is an amazing program that I will never fully appreciate.


----------



## ProWallGuy (Apr 7, 2007)

daArch said:


> thank you Rent,
> 
> 
> I knew there was a way to easily get the first and last names delineated into separate columns, but didn't realize it was that simple.
> ...


I appreciate the tutorial, but how is doing all those steps easier that just cutting and pasting the last names into another column? Maybe if I had thousands to do...


----------



## PatsPainting (Mar 4, 2010)

ProWallGuy said:


> I appreciate the tutorial, but how is doing all those steps easier that just cutting and pasting the last names into another column? Maybe if I had thousands to do...


You can create a macro that would be much eaiser and you could swap back and forth anytime you want 

Just replace the Range to the columns where you have the names at

```
Dim ProWallGuy As Variant
For Each x In Range("A1:A500") 
     ProWallGuy = Split(x.Value, " ")
     If x.Value <> Empty Then
         If UBound(ProWallGuy) - LBound(ProWallGuy) + 1 > 1 Then
         x.Value = ProWallGuy(1) & " " & ProWallGuy(0)
         End If
     End If
 Next
```
Pat


----------



## ProWallGuy (Apr 7, 2007)

Yeah, as soon as I travel to Greece and learn their language, I'll come back and read this thread again and maybe at that point it will make sense.


----------



## daArch (Mar 15, 2008)

Tim,

as we discussed, the meat of your goal is to put the first name in one column and the last name in another. I thought you needed to add a comma or other delimitator but the space suffices

These simple steps do what you wanted:
----------

1) Select the column that has the names by clicking on the column letter

2) Go to the "Data" tab

3) Click on "Text to Column" in the "Data Tools" group




The "Convert Text to Column" wizard will open.

1) Select "Delimited" if not already selected

2) Click "Next"

3) In the "Delimiters" box, un-check everything and ONLY select "Space"

4) Click "Next"

5) Finally, click "Finish"

------------

The rest of what he described, was to put the names BACK in one column with last name first, comma, first name. But you wanted two columns, so that can be ignored.

3 - 4 minutes for the unexperienced, one or two minutes once you know what you are doing. I tested it. 


With H. G. Wells and F. Scott Fitzgerald, those names will be separated at each space, you will have H. & F in one column, G. & Scott in another, and Wells & Fitzgerald in yet another. Besides a manual fix, I am sure there is a simple way to adjust how Excel handles that.


----------



## PatsPainting (Mar 4, 2010)

Bill, If you are bored give the macro a shot. This way he can keep the one column and switch back and forth with a simple shortcut key. 

Steps to create a Macro.

1. Click the Developer Tab and click the record macro button

2. A box will appear, you can change the name or leave it, add a short cut key. Then click the Ok button.

3. Now click the stop recording button at the top.

4. Now click the large macro button next to the visual basic button, select your macro and choose the edit button.

5. Copy the code I posted in the early post and past it in the window so it will look like this.

```
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+a
'
Dim ProWallGuy As Variant
For Each x In Range("A1:A500")
     ProWallGuy = Split(x.Value, " ")
     If x.Value <> Empty Then
         If UBound(ProWallGuy) - LBound(ProWallGuy) + 1 > 1 Then
         x.Value = ProWallGuy(1) & " " & ProWallGuy(0)
         End If
     End If
 Next
End Sub
```
6. Your done, click the play button to make sure all is good. Go back to your worksheet and hit the Ctrl+a shortcut and that column will swap back and forth.

Pat


----------



## PatsPainting (Mar 4, 2010)

Here is a breakdown of the code


```
[SIZE=2][COLOR=Navy]We will declare our array and we will call it ProWallGuy [/COLOR][/SIZE]
Dim ProWallGuy As Variant 
[SIZE=2][COLOR=Navy]We will loop through each cell in column A - cells A1 through A500[/COLOR][/SIZE]
For Each x In Range("A1:A500")
  [SIZE=2][COLOR=Navy]We will now fill our array with the contents of the cell. We will split up by the number of spaces. for example "Harry Homeowner" will be split in two so now ProWallGuy(0) will be Harry and ProWallGuy(1) will be Homeowner[/COLOR][/SIZE]
     ProWallGuy = Split(x.Value, " ")
    [SIZE=2][COLOR=Navy] If the value in the cell is empty lets do nothing[/COLOR][/SIZE]
     If x.Value <> Empty Then
    [SIZE=1][COLOR=Navy][SIZE=2] Getting the size of the array, we want to make sure its more then 1 otherwise what is the point here. [/SIZE]  [/COLOR][/SIZE]
     If UBound(ProWallGuy) - LBound(ProWallGuy) + 1 > 1 Then
         [SIZE=2][COLOR=Navy]Now lets do the swap[/COLOR][/SIZE]
         x.Value = ProWallGuy(1) & " " & ProWallGuy(0)
         End If
     End If
 Next
```
Pat


----------



## daArch (Mar 15, 2008)

Thank you Pat,yah, when I'm bored <G>
Macros have scared me - one of those fears of the unknown. When ever I study them, I can't just accept they will work, I need to know what things stand for, like "dim", "UBound", and "LBound". The logic is easy, the nomenclature is like a foreign language. 

I'll have to find a place to save all these tips, because I can't retain but I will need them in the future.

BTW, when Tim called and asked how to do it, and I wasn't much help, he said he was going to post the question here. He DID use you as an example of someone who would have an answer.

I have to admit now, he was correct :thumbup:


----------



## chrisn (Jul 15, 2007)

daArch said:


> Thank you Pat,yah, when I'm bored <G>
> Macros have scared me - one of those fears of the unknown. When ever I study them, I can't just accept they will work, I need to know what things stand for, like "dim", "UBound", and "LBound". The logic is easy, the nomenclature is like a foreign language.
> 
> I'll have to find a place to save all these tips, because I can't retain but I will need them in the future.
> ...


if you uderstand Greek


----------



## NEPS.US (Feb 6, 2008)

chrisn said:


> if you uderstand Greek


I had a Greek girlfriend once. Boy that was fun.


----------



## daArch (Mar 15, 2008)

NEPS.US said:


> I had a Greek girlfriend once. Boy that was fun.


did she Excel ?


----------



## NEPS.US (Feb 6, 2008)

daArch said:


> did she Excel ?


.................


----------



## PatsPainting (Mar 4, 2010)

Freaking painters.....


----------



## chrisn (Jul 15, 2007)

PatsPainting said:


> Freaking painters.....[/QUOTE]
> 
> 
> at least most of us don't wear water melons on our heads


----------



## epretot (Dec 17, 2011)

PatsPainting said:


> Freaking painters.....


Can your write a macro to paint a room?


----------



## daArch (Mar 15, 2008)

epretot said:


> Can your write a macro to paint a room?



there's an app for that


----------



## Workaholic (Apr 17, 2007)

NEPS.US said:


> I had a Greek girlfriend once. Boy that was fun.


So exotic. I had a hispanic gf before she had a temper and a lot of issues. 



NEPS.US said:


> .................


Before the edit was hilarious. :thumbsup:


----------



## daArch (Mar 15, 2008)

Workaholic said:


> So exotic. I had a hispanic gf before she had a temper and a lot of issues.


so you are saying that AFTER you had her, she developed a temper and issues?


----------



## Workaholic (Apr 17, 2007)

daArch said:


> so you are saying that AFTER you had her, she developed a temper and issues?


Probably, I think she went through a drunk phase for awhile after we split. Damn Daddy issues.


----------



## daArch (Mar 15, 2008)

Workaholic said:


> Probably, I think she went through a drunk phase for awhile after we split. Damn Daddy issues.


how much younger WAS she than you?


----------



## Workaholic (Apr 17, 2007)

daArch said:


> how much younger WAS she than you?


A bit when I was 25 she was 19. I could always get away from her when I needed to by popping into the bar and playing video poker lol. It was a short term relationship, probably 4 or 5 months.


----------



## daArch (Mar 15, 2008)

Workaholic said:


> A bit when I was 25 she was 19. I could always get away from her when I needed to by popping into the bar and playing video poker lol. It was a short term relationship, probably 4 or 5 months.


ah yes the advantage of underage dating.

sheeet, when I was 25, 4 or 5 months was an eternity. Now I can't even get ....... oh never mind


----------



## Workaholic (Apr 17, 2007)

daArch said:


> ah yes the advantage of underage dating.
> 
> sheeet, when I was 25, 4 or 5 months was an eternity. Now I can't even get ....... oh never mind


why does this ........ always eat the best part of the sentences.


----------



## daArch (Mar 15, 2008)

Workaholic said:


> why does this ........ always eat the best part of the sentences.


discretion ????



*NAH* :no:


----------



## NEPS.US (Feb 6, 2008)

Workaholic said:


> So exotic. I had a hispanic gf before she had a temper and a lot of issues.
> 
> 
> Before the edit was hilarious. :thumbsup:


Oh crap - you guys can see those?


----------



## Workaholic (Apr 17, 2007)

Nah, just the wrong section of the forum.


----------



## NEPS.US (Feb 6, 2008)

Workaholic said:


> So exotic. I had a *hispanic* gf before she had a temper and a lot of issues.


I had one of those too. She was like the town bike, everyone had a ride.


----------



## daArch (Mar 15, 2008)

NEPS.US said:


> Oh crap - you guys can see those?


we also Excel


----------



## daArch (Mar 15, 2008)

NEPS.US said:


> Oh crap - you guys can see those?


it's really fun to write nasty things about certain mods and then edit it because I KNOW certain ones will be snooping at the original comment. :thumbup:


----------



## Workaholic (Apr 17, 2007)

NEPS.US said:


> Oh crap - you guys can see those?


One of the perks. 


NEPS.US said:


> I had one of those too. She was like the town bike, everyone had a ride.


She could of drug you on the Maury show and you could of jumped up and down that you are not the father lol.


----------



## Workaholic (Apr 17, 2007)

daArch said:


> it's really fun to write nasty things about certain mods and then edit it because I KNOW certain ones will be snooping at the original comment. :thumbup:


I read the edits, good stuff sometimes.


----------



## NEPS.US (Feb 6, 2008)

daArch said:


> it's really fun to write nasty things about certain mods and then edit it because I KNOW certain ones will be snooping at the original comment. :thumbup:


I had to edit a post about Bill tonight. I'll send a bottle of Jim Beam tomorrow.


----------



## Workaholic (Apr 17, 2007)

NEPS.US said:


> I had to edit a post about Bill tonight. I'll send a bottle of Jim Beam tomorrow.


Bill always the lucky one. just send him 99% of the Beam


----------



## daArch (Mar 15, 2008)

NEPS.US said:


> I had to edit a post about Bill tonight. I'll send a bottle of Jim Beam tomorrow.


DAMN, Now I'll be up all night searching.


reminds me of the old gag, 

On *both *sides of an index card you write:


*How to keep a New Yorker occupied for hours .....

turn over*​
and then give it to a Yankee fan


----------



## PatsPainting (Mar 4, 2010)

NEPS.US said:


> Oh crap - you guys can see those?


Just finished a little macro to read the edits. Was a little more challenging then I thought. I like your reverse logic there but kinda makes me wonder who wears the pants in your household :whistling2:

Pat


----------



## NEPS.US (Feb 6, 2008)

PatsPainting said:


> Just finished a little macro to read the edits. Was a little more challenging then I thought. I like your reverse logic there but kinda makes me wonder who wears the pants in your household :whistling2:
> 
> Pat


Dork.


----------

