how to split a word in Quotation


In one column there is a word like this  "username:"jonasbjerre"

How can I  split only the word  jonasbjerre using a formula?please any help.I do have more than 500 in such  "username:"Kibraru" different form in one column(excel and google sheeet).  I need to split and organize them with out the quotation.

Below is a sample

"username:"aaryan_varshney" "username:"Kibraru" "username:"Bestk233" "username:"Superstar2020" "username:"456ttt" "username:"dd" "username:"4fgkazizzzz" "username:"sawetu-23ft" "username:"Wrldmannnnnnnn" "username:"BRAVE_12123" "username:"winnnnnnnnnnnnnnn"



Selected Answer

Please try this formula in row 2 and copy down as required.

=TRIM(SUBSTITUTE(MID(A2,FIND(":", A2)+1,50),"""",""))

Note that the "50" in the formula specifies a maximum number of characters. You can choose a larger number because it's insignificant as long as the string you want has fewer characters. However, if it's longer it will be truncated.



Amazing!!!!Thank you
Lawdej (rep: 2) Sep 22, '20 at 12:53 am
Add to Discussion

Select the data, go to Data tab > Text to Columns > choose Delimited on the first screen and then check next to Space and uncheck the other options and click Finish.

Then hit Ctrl + H to go to Find/Replace and in Find what, input "username:" and leave Replace with blank and hit Replace All. Then do the same thing with a single " to remove the last quotation.

That should do it. Try it on sample data first.


Looks like you don't need to do the Text-to-Columns part on the data in your files but the find replace will work just fine.



This is really helpful. Thank you
But I was looking for a formula.
Lawdej (rep: 2) Sep 22, '20 at 1:18 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login