cut text from part of a cell and put it into another

[jgrotter]

I'm new again to VBA, there are a plethora of commands that I don't know anything about.
Here's my situation:
In Excell: I'm trying to take some of the text out of a cell, leave some of the text in there still, and paste the text that was cut into another cell. The text that I'm starting out with in the beginning cell is delimited with a space, but of variable length. Like this:
a1: Frank Johnson
a2: Guy Smith
a3: John Galt
etc...
want them to be...
a1: Johnson b1:Frank
a2: Smith b2:Guy
a3: Galt b3:John
etc...
I can cut and paste, but I am trying to actually understand what's going on, and so far, searching through the posts, I can't figure out how to do that.
Please help.

 

[jgrotter]

By the way, I'm trying to do all this with a macro, in case I wasn't clear about that in the first post.
I can cut and paste code into a macro, but I'm trying to understand it.
Sorry, and thanks again.

 

[wwgmr]

Hi JGrotter

There is built in Excel functions that can do this for you. So there should be way to use the functions in your vba

The function I know so here it is

If in colum A you have your names You can extract into column say C their first name by this Function line in that column Starting in C1 place this
=LEFT(a1,FIND(" ",a1))
then in the next column place this code to get the last name.
=RIGHT(a1,(LEN(a1)-FIND(" ",a1)))

What we are doing is in first one we are telling excel to start on left side of that cell A1 and return all charcters up to # number. We get # buy finding the Space between the first name and last using find. Which gives us that number.
To get last name we do that buy starting right side of cell and subtracting the position of the space say 4 from the total length.

If you need to run that from code I am sure someone will post better solution soon if not already. lol

If you still want in code I will see if I can figure it out. Still learning that side of excel.

 

[SkipVought]

Hi,

I would not recommend cut 'n' paste.

Rather I recommend the Text to Columns Wizard in the Data menu, using a Space delimiter.

The caveat might be, with WHATEVER technique you might use, that some names have spaces in then, or you might have a middle initial or a Jr, or III.

You could also use the Left() and Right() functions.

Almost anything that you can do on a spreadsheet, you can record using the macro recorder (menu Tools/Macro)

You can lean ALOT by examining the cord that is generated, readin, asking questions etc.

Post back if you have more questions





Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jgrotter]

Thank you, I think this can help with what (little) I know about vba (somewhere i read that there isn't a "find" for vba, but there's, instead, an "instr&quot.
Each piece of advice is another that will add.
Thank you, wwgmr.

 

[jgrotter]

Thank you skip, I'll take a look at that, too.

 

[jgrotter]

ok. The text to columns wizard does what I wanted after a little tweaking. Thank you very much.

 

[wwgmr]

Great Feature I forgot about Skip Thanks! Sometimes I make more work for myself lol