Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

how to split a word in Quotation

0

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"

excel
MS Office Version: 2016
posted by: Lawdej (rep: 2) on Mon Sep 21, 2020 at 9:35 am
Answer
Discuss

Answers

0
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.

posted by: Variatus (rep: 4889) on Mon Sep 21, 2020 at 9:26 pm
Discuss

Discussion

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

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.

Update

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.

posted by: don (rep: 1989) on Mon Sep 21, 2020 at 5:09 pm
Discuss

Discussion

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