Hashtag Separation

[Shri]

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]

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]

Hi Shri, welcome to the forum

Assume your data in A2, you can use the following 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]

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]

Hi,

Use the below

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

 

[Shri]

WOW,

Thanks a lot. Its works like magic. You saved my day.

 

[Asheesh]

Great, you welcome...Keep visiting !!