Cutting and Pasting Text from one cell and moving one column to left

**galca002** · 05-31-2010, 10:25 PM

Hello everyone,
I have a transcript that I am helping correct. It is currently set up like this per cell:

|-------------------------------------Column 1---------------------------------------|
Counselor: (response). Skill: (response). Behavior: (response).

Right now, the document is just one column and each cell is set up in this manner.

I simply want to be able to cut everything to the right of the word "Skill:", including the word itself and move it over to the next column. I also plan to repeat this again for the "Behavior:" one.

That way, it will now be:

|------Column 1----| |----Column 2------| |--Column 3----|
Counselor: (resp.) Skill: (resp.). Behavior: (resp.)

Each Counselor, Skill, and Behavior response would now be in its own Column

Is this possible? I'm somewhat new to Excel and I had trouble looking up the question and apply it to my situation. You would save me hours of work if you were able to do this. Thanks :D

**JBeaucaire** · 05-31-2010, 10:35 PM

With that text string in A1...

In B1:
=LEFT(A1,FIND("Skill:",A1)-2)

In C1:
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,""),D1,""))

In D1:
=MID(A1,FIND("Behavior:",A1),LEN(A1))

The formula in C1 won't look right until after you put the formula in D1.

Now, copy those three cells down as far as needed for all your column A strings.

Then copy Columns B:D, then Edit > Paste Special > Values to remove the formulas.

Delete column A...done.

**galca002** · 05-31-2010, 10:53 PM

That works perfectly! I have one more transcript that has an additional value.

Can you tell me what the code would be to have it be the following format?

|---Column1--| |--Column2--| |--Column3--| |--Column4--|
Counselor: ( ) Skill: ( ) Eval.: ( ) Bx.: ( )

Thank you!

**JBeaucaire** · 05-31-2010, 11:18 PM

In B1:
=LEFT(A1,FIND("Skill:",A1)-2)

In C1:
=MID(A1,FIND("Skill:",A1),FIND("Eval.:",A1)-FIND("Skill:",A1))

In D1:
=MID(A1,FIND("Eval.:",A1),FIND("Bx.:",A1)-FIND("Eval.:",A1))

In E1:
=MID(A1,FIND("Bx.:",A1),LEN(A1))

**galca002** · 05-31-2010, 11:37 PM

Thank you, that worked great :D I have a question though. I can't get the Copy Special -> Value thing to work when I try to do it for all of the cells at once. It will work if I do each cell individually though but since I have 400 rows and 4 columns, that seems like alot. Can you clarify on how to do that for all of the cells at once? Sorry

**JBeaucaire** · 06-01-2010, 02:27 AM

1) highlight all the cells
2) press ctrl-c
3) click on Edit > Paste Special > Values

**galca002** · 06-01-2010, 04:22 AM

Thank you :D

**vixius** · 07-09-2010, 09:57 AM

HI, I need something similar...
I got addresses to elaborate!

Column A:
Restaurant out of every concept and stuffs

So, I got to cut the string because the printer blocks if I send addresses max of 47 char (334 pixel) and i want cut some words and put them in the column B, but i don't want lose the mean of the address.

example:

I want cut and get in the column B :"concept and stuffs"
I need a function or whatever to fix it for every address that i get from the Post.
Thank You!