+ Reply to Thread
Results 1 to 11 of 11

I have some cells that contain HTML - Can I push H2s to their own column?

Hybrid View

sewercider I have some cells that... 08-16-2022, 07:38 PM
HansDouwe Re: I have some cells that... 08-16-2022, 08:06 PM
sewercider Re: I have some cells that... 08-17-2022, 02:43 AM
JohnTopley Re: I have some cells that... 08-17-2022, 02:53 AM
HansDouwe Re: I have some cells that... 08-17-2022, 04:34 AM
sewercider Re: I have some cells that... 08-17-2022, 06:37 AM
AliGW Re: I have some cells that... 08-17-2022, 02:52 AM
Glenn Kennedy Re: I have some cells that... 08-17-2022, 06:56 AM
sewercider Re: I have some cells that... 08-17-2022, 07:16 AM
Glenn Kennedy Re: I have some cells that... 08-17-2022, 07:25 AM
sewercider Re: I have some cells that... 08-17-2022, 06:27 PM
  1. 08-16-2022, 07:38 PM #1
    sewercider is offline
    Registered User
    Join Date
    10-22-2018
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    17

    I have some cells that contain HTML - Can I push H2s to their own column?

    Hi all,

    This is one is a bit of a long shot!

    I basically have a column where each cell contains some HTML I have scraped from a web crawl.

    Sample of a cell below: 

    <h2>Allowances- Payroll Forms</h2>

<p><a href="file.docx">Download</a><br>

<em>Last updated 17 Feb 2020 5:11pm</em></p>

<h2>Deductions- Payroll Forms</h2>

<p><a href="file.docx">Download</a><br>

<em>Last updated 17 Feb 2020 5:11pm</em></p>
    Is it possible to extract the inner text from each H2 and move to its own column for each row?

    Any help would be greatly appreciated!

    With thanks,
    Cas
    Reply With QuoteRegister To Reply
  2. 08-16-2022, 08:06 PM #2
    HansDouwe is offline
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: I have some cells that contain HTML - Can I push H2s to their own column?

    Try this formula
    Formula: copy to clipboard 
    =IFERROR(MID(A1,SEARCH("<h2>",A1)+4,SEARCH("</h2>",A1)-SEARCH("<h2>",A1)-4),"")
    I'm looking forward for your feed back.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 08-17-2022, 02:43 AM #3
    sewercider is offline
    Registered User
    Join Date
    10-22-2018
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    17

    Re: I have some cells that contain HTML - Can I push H2s to their own column?

    This works really well, however I meant that all of the HTML in the example was in 1 cell - If I run your formula on that HTML in one cell it only finds the first H2. Is there a way to find all of them, and maybe separate with a comma or something?

    Amazing work!

    With thanks,
    Cas
    Last edited by AliGW; 08-17-2022 at 02:51 AM. Reason: Please DON'T quote unnecessarily!
    Reply With QuoteRegister To Reply
  4. 08-17-2022, 02:53 AM #4
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,720

    Re: I have some cells that contain HTML - Can I push H2s to their own column?

    Please post a sample file: see yellow banner at top of page for instructions.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  5. 08-17-2022, 04:34 AM #5
    HansDouwe is offline
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: I have some cells that contain HTML - Can I push H2s to their own column?

    Quote Originally Posted by sewercider View Post
    Is there a way to find all of them,
    Yes, there is: Copy the formulas down.
    Reply With QuoteRegister To Reply
  6. 08-17-2022, 06:37 AM #6
    sewercider is offline
    Registered User
    Join Date
    10-22-2018
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    17

    Re: I have some cells that contain HTML - Can I push H2s to their own column?

    Hey sorry for the confusion - I have worked out how to attach an example. Thank you!
    Attached Files Attached Files
    Last edited by AliGW; 08-18-2022 at 01:55 AM. Reason: Please DON'T quote unnecessarily!
    Reply With QuoteRegister To Reply
  7. 08-17-2022, 02:52 AM #7
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,375

    Re: I have some cells that contain HTML - Can I push H2s to their own column?

    Provide a sample workbook that shows clearly what you have and what you want, mocked up.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  8. 08-17-2022, 06:56 AM #8
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: I have some cells that contain HTML - Can I push H2s to their own column?

    You didn't tell us HOW you wanted the results to be displayed. This:

    =TRANSPOSE(FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"<",""),"/h2>","h2>"),"h2>","</B><B>")&"</B></A>","//B[not(contains(., 'p>a'))][position()>1]"))

    returns them in succeessive columns to the right.
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  9. 08-17-2022, 07:16 AM #9
    sewercider is offline
    Registered User
    Join Date
    10-22-2018
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    17

    Re: I have some cells that contain HTML - Can I push H2s to their own column?

    Thanks for this - I am have trouble replicating it unfortunately. It seems to only return the first H2 for me (see attached). Is it possible to return all the results to one cell rather than multiple columns? I have included a 'preffered' output, but if it is easier to do by column as you have suggested, I am happy with this solution

    With thanks
    Attached Files Attached Files
    Last edited by AliGW; 08-18-2022 at 01:55 AM. Reason: Please DON'T quote unnecessarily!
    Reply With QuoteRegister To Reply
  10. 08-17-2022, 07:25 AM #10
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: I have some cells that contain HTML - Can I push H2s to their own column?

    Try it now.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 08-17-2022, 06:27 PM #11
    sewercider is offline
    Registered User
    Join Date
    10-22-2018
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    17

    Re: I have some cells that contain HTML - Can I push H2s to their own column?

    Cheers! Strangely enough when I pasted my data in the previous example everything worked... So just copied it back into my main spreadsheet then. Thanks so much for your help, this is a really cool solution
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Linking 2 cells to pull and push values into each other
    By Rince91 in forum Excel General
    Replies: 3
    Last Post: 05-31-2016, 04:32 PM
  2. Insert cell (shift cells right) and format at the push of a button!
    By seanppk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-05-2016, 05:10 AM
  3. Unable to push a gif below cells
    By Oblisgr in forum Excel General
    Replies: 4
    Last Post: 12-24-2012, 05:29 PM
  4. convert formated excel column to html source text with html tags
    By julia81 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-19-2011, 04:22 PM
  5. [SOLVED] Push data to the right of a range of cells
    By Hyflex in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-22-2011, 06:37 PM
  6. push data to another worksheet based on a choice of 4 cells being checked
    By jayers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-02-2009, 09:34 AM
  7. [SOLVED] Pasting HTML into the cells in column A
    By Colin Hayes in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-22-2006, 09:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1