Help with Excel Text to Column

[Maverick27]

G'Day Excel Gurus

I need help in converting selective cell data from Text to Column.

The Column AF data looks the screenshot below:

Anyone care to share a Macro Code (or Excel Function) to convert Text to Column for the following data :

• Email ( 1st instance )
• Facebook
• Instagram
• Web

The Final excel output should look like:

Thanks in Advance.

 

[Maverick27]

FYI - Column AF with 510 rows

 

[Maverick27]

I would to show the 1st instances of the email:

So, the final output of Text to Columns should be:

• Email ( 1st instance )
• Email ( 2nd instance )
• Facebook
• Instagram
• Web

 

[Maverick27]

Anyone ?

 

[Alex Blakenburg]

I will send you some code tomorrow.

 

[RoryA]

Ag2:

=TRIM(MID(FILTERXML("<a><b>"&SUBSTITUTE($AF2,";","</b><b>")&"</b></a>","//b[starts-with(., '"&AG$1&"')][1]"),LEN(AG$1)+2,100))

Copy across and down. You may need error handling in case one of the items doesn't exist.

 

[Maverick27]

Ag2:

=TRIM(MID(FILTERXML("<a><b>"&SUBSTITUTE($AF2,";","</b><b>")&"</b></a>","//b[starts-with(., '"&AG$1&"')][1]"),LEN(AG$1)+2,100))

Copy across and down. You may need error handling in case one of the items doesn't exist.

Unfortunately, I'm getting a #VALUE! error all the way down.

I'm quite familiar with Excel Functions, but I'm quite rusty at the moment.

Would'nt it be simpler to use a Extract Substring function to isolate the Facebook, Instagram, Email Text..etc from the string & output to a cell ?

Just my thoughts.

​

 

[Maverick27]

Unfortunately, I'm getting a #VALUE! error all the way down.

View attachment 75380

I'm quite familiar with Excel Functions, but I'm quite rusty at the moment.

Would'nt it be simpler to use a Extract Substring function to isolate the Facebook, Instagram, Email Text..etc from the string & output to a cell ?

Just my thoughts.

​

Here's the correct screenshot. Pls ignore the previous.

 

[RoryA]

Either change AI1 to Email as it was in the original sample data, or use the literal text in the formula instead of a cell reference. The case needs to match the cell content.

 

[Maverick27]

Either change AI1 to Email as it was in the original sample data, or use the literal text in the formula instead of a cell reference. The case needs to match the cell content.

Hi Rory

Do you mind copying & pasting the String Text below in your Excel & show me the final output ?

Not sure what function is doing ? I want to extract the Email string, Facebook link, Instagram link.....etc into different cols.

Email:info@5senses.co.ke ; Email:reservations@5Senses.co.ke ; Email:x ; Facebook:www.facebook.com/5SensesKe ; Instagram:www.instagram.com/5_senses_ke ; Linkedin:www.linkedin.com/company/five-senses-restaurant ; Twitter: www.twitter.com/5SensesKe ; Web: www.5senses.co.ke