Find replace and move to new cell?

[Musomox]

Hi

I have a column that contains mixed text, sometimes including a facebook url of the form facebook.com/name and sometimes twitter names of the form @name.

I want to remove the facebook and twitter details from these cells and put the info into new cells on the same row.

In the case of the facebook url I would like to append https://

In the case of the twitter name I want to strip the @ and replace it with https://twitter.com/

I was looking at the find and replace using regex, but I couldn't see a way to put the text into new cells, and when the name part was the same for both facebook and twitter it screwed up.

Is it possible to do what I want using Find and Replace? Is there a better way to do this?

The purpose here is to process an exported mysql database table imported into OpenOffice as a csv file, find and move the data (amongst other operations, and then re-import the data back into mysql. This means any processing must export the final data... is this compatible if formulae were used?

Thanks for your help!

Cheers!

[acknak]

Greetings and welcome to the community forum!

I usually approach this "in reverse": copy all the data to a new column, then use find/replace to remove the text I don't want.

For example, this should leave all the twitter handles:

1) Select the cells you want to change
2) Edit > Find & Replace
Search for: ^[^@]*(@\S+).*
Replace with: $1
Options/Match case: ON
Options/Current selection only: ON
Options/Regular expressions: ON

Click Replace All

NOTE: This only works when there is at most ONE match in the text. If there is more than one, or if there are mixed twitter/facebook IDs in the same cell, it will take more passes—or maybe a different tool.

[Musomox]

Hi, and many thanks for your welcome!

Ok, what you say about using intermmediate cells makes sense. I will give it a shot and see how it goes. If I have to do anything else on top of your suggestion I'll post it back here. If I can't solve the problem... likewise lol

Good to meet you!

[acknak]

If you get stuck and need more specific suggestions, it's probably simplest to paste in some actual data that you're working with—or attach a sheet with some sample data.

[Musomox]

Hi

Ok... not getting very far.

Here is an example piece of input data:

"Punk is dead. Your friends sell real estate. Listen to a steaming pile of hate, Chicago's number one Hardcore/Underground Punk show. Get back to the music you loved before you sold out. facebook.com/wxxm.chicago @wxxm"

what I need is to strip the facebook and twitter data from the original:

"Punk is dead. Your friends sell real estate. Listen to a steaming pile of hate, Chicago's number one Hardcore/Underground Punk show. Get back to the music you loved before you sold out."

and to process the facebook url to become:

https://facebook.com/wxxm.chicago

and twitter to become:

https://twitter.com/wxxm

Many thanks for your help!

[Musomox]

I should add that I intend moving the facebookand twitter urls to their own columns

[MaheshMusic]

Hi guys,

I work with the OP here and I've been able to make progress on this issue though I have reached a block.


I replaced all the cells containing twitter handles (@ExampleHandle) with http://twitter.com/ExampleHandle by running a regular expression. Once it is done, all these cells containing the twitter urls are highlighted. I would like to move these non-contiguous cell contents (in a column) to the respective adjacent cells in ANOTHER column.

For example if D5, D7, D21, D23 are the cells that are selected. I want to move them all to let's say A5, A7, A21 and A23. If I simply paste them over to another column, they seem to be pasted consecutively and NOT in their respective positions. This might be a silly one if so, I apologise. But I can't seem to get my head around it.

Any help would be appreciated. Thanks!

Mahesh

[acknak]

Again, the answer is to move/copy ALL the cells to the new column, then clear the cells that don't contain a match. This would be easier if you keep the original Twitter handles. Then you can easily search for text that does NOT contain "@": ^[^@]+$

And you can construct the urls from the handles with a formula. If the handle is in A1,
=SUBSTITUTE(A1;"@";"http://twitter.com/")
will convert it to the url form.