• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hashtag Separation

Shri

New Member
I have twitter posts in rows which contains Hashtags

e.g. I have data in column A
RT @Accenture The evolving #IoT compels #digital business to think & act creatively to fend off threats. http://t.co/ZqV4vVhH3I http://t.co/OILaoW8rEh
RT @Accenture The evolving #IoT compels #digital business to think & act creatively to fend off threats. http://t.co/ZqV4vVhH3I http://t.co/OILaoW8rEh
KirkDBorne: Can you crack this #BigData #Analytics Challenge at KPMG? http://t.co/l4erbOc6w1 #abdsc #DataScience
Global Mobile Innovator Challenge! Join in! #mobile #iot #ibm #developers https://t.co/g1BQ3ob7tF

Now i want each hashtag in separate cell like if i have data in A2 cell i want hashtags from A2 in B2, C2, D2 cell depending upon number of hashtags. Tried some macros and formulas from google search but couldn't make it clean.

P.S. I am not pro in macros. But i know how to use them. (cant write on my own)
Please provide excel formulas or macro.
 

Asheesh

Excel Ninja
Hi Shri,

Welcome to the forum

Suppose your data is in A1 put the below formula in B1 and drag it towards right..

TRIM(MID(SUBSTITUTE($A1,"#",REPT(" ",LEN($A1))),(LEN($A1)*(COLUMN(A1)-1))+1,LEN($A1)))

If this doesn't help..share the sample file with the expected result..
 

Khalid NGO

Excel Ninja
Hi Shri, welcome to the forum :awesome:

Assume your data in A2, you can use the following code:

Code:
Function STR_SPLIT(str, sep, n) As String
    Dim V() As String
    V = Split(str, sep)
    STR_SPLIT = V(n - 1)
End Function
Now use the following formula in B2:
=STR_SPLIT($A2,"#",COLUMN(A1))
Copy to right

Copy from

Regards,


Edit: Didn't noticed Asheesh already replied.
thanks Asheesh for the formula solution.
 

Shri

New Member
Hi Shri,

Welcome to the forum

Suppose your data is in A1 put the below formula in B1 and drag it towards right..

TRIM(MID(SUBSTITUTE($A1,"#",REPT(" ",LEN($A1))),(LEN($A1)*(COLUMN(A1)-1))+1,LEN($A1)))

If this doesn't help..share the sample file with the expected result..
Thanks Ashish for your help, formula is working but I want other way. I want result as #text1, #text2 only. I want words which comes after #.

eg. It Twitter posts in A1 is "I am very happy today I cleared the final exam #Feelingproud #daytoremember

so I want result in B1as "Feelingproud" and in C1 "daytoremember"

Thanks
 

Asheesh

Excel Ninja
Hi,

Use the below

TRIM(MID(SUBSTITUTE($A1,"#",REPT(" ",LEN($A1))),LEN($A1)*COLUMN(A1),LEN($A1)))
 
Top