Mass text isolation and copying

[NightSprite]

OK, I've just cut and pasted a large block of information into Excel 2010

This has all dropped into one column and I'm slowly sorting through it all.

But one lot of information in over 500 cells in column starts with an @ symbol (It's a twitter handle). I want to isolate that string of text @(twitterhandle), remove it from column A and move it into column B.

Is there a quicker way to do this without having to individually going through all 500 cells in column A by hand without ruining the other text in those cells?


Once again, thank you.

[ChemistB]

You just want to isolate the handle so there will be no spaces in it. It could appear anywhere in your block of text or is always at the beginning or end?

This should extract that substring

=MID(A1, FIND("@",A1), FIND(" ",A1, FIND("@",A1)+1)-FIND("@",A1))

Does that work for you?

[NightSprite]

Since it's a Twitter handle the text has no spaces in it. It is pretty much "@KineticEntity" that I want harvest and move, for example. Sometimes the text is at the end, but if the Twitter account is protected it is in the middle of the block of text.


That account is my own and is therefore safe for me to share!

[newdoverman]

Going by what I could find out about twitter handles, the "@dsfsdfd" starts the message. If that is the case, then this will extract that portion to column B if the text is in column A

=MID(A1,1,SEARCH(" ",A1))

If you need the rest of the text without the "handle" then enter this in column C and copy down.

=MID(A1,SEARCH(" ",A1),LEN(A1)-SEARCH(" ",A1))

[NightSprite]

Ok, I did that and the wrong text got moved. What am I doing wrong here?

And the moved text wasn't removed from column A, just copied.

TwitterDirectoryTestBed.xlsx


Edit to add: This is a response to Newdoverman's response

Secondary edit: ChemistB, this is what I got with your string.

TwitterDirectoryTestBed2.xlsx

[ChemistB]

If you don't have a number pad, try this formula

=MID(A1, FIND("@",A1), FIND(" ",SUBSTITUTE(A1, CHAR(160)," ")&" ", FIND("@",A1)+1)-FIND("@",A1))

[ChemistB]

Okay, some of your spaces are regular spaces (ASCII Code 32) and others are "internet" spaces (Code 160). My formula will only find the regular spaces so we need to replace the internet spaces with regular spaces.

If you have a numberpad, you can use FIND/Replace
Select Column A where your text is.
CNTRL H to open up find and replace window
In the FIND box, on the numberpad, hold down ALT and type 0160
You should see the cursor move to the right as it types the internet space
Then in the Replace box, just put a space
Replace all.

Also, we need to put a space at the end of your string in the formula to avoid that error you got in B2. Modify as so;

=MID(A1, FIND("@",A1), FIND(" ",A1&" ", FIND("@",A1)+1)-FIND("@",A1))

[NightSprite]

That is awesome, thank you.

Now I have to figure out how to remove that text from column A.

Thanks for the hard part though.

[ChemistB]

Once you have what you want in B, Copy and Paste Special > Values to remove the formulas. Then you can clear A

Glad to help

[NightSprite]

Ahh, but I still want to keep the name in column A, seperate from the handles!

[ChemistB]

Where is the name in relationship to the handle? Does it move around also?

[NightSprite]

The name is always at the start of the string of text.

[ChemistB]

If the name doesn't have spaces, you could use a similar formula to the ones above. If sometimes it does have spaces and sometimes it doesn't, then it's a bit tougher.