clean up excel column in Alteryx

[nehashah]

HI, I would like to clean up data which comes in email format, see below sample email. I would like to get info what is in bold text into Separate column and everything else should be remove, how should i do it in Alteryx. Thank you for your help.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

<center>
<table border="0" style="height:100%;font-family:Helvetica;margin:0;padding:0;background-color:#d0cccc;border-collapse:collapse;width:100%;">
<tr>
<td style="font-family:Helvetica;margin:0;padding:20px;height:100%;width:100%;">
<table border="0" style="font-family:Helvetica;width:750px;border:1px solid #BBBBBB;border-collapse:collapse;">
<tr>
<td style="font-family:Helvetica;">

<table border="0" style="font-family:Helvetica;background-color:#FFFFFF;border-bottom:3px solid #ff6e1e;border-collapse:collapse;">
<tr>
<td style="padding-top:10px;padding-right:20px;padding-bottom:10px;padding-left:20px;font-family:Helvetica;color:#808080;font-size:10px;line-height:125%;text-align:left;">

</td>
</tr>
</table>

</td>
</tr>

<tr>
<td style="font-family:Helvetica;">

<table border="0" style="font-family:Helvetica;background-color:#FFFFFF;border:20px solid #F4F4F4;border-collapse:collapse;">
<tr>
<td colspan="3" style="font-family:Helvetica;color:#505050;font-size:14px;line-height:150%;padding-top:20px;padding-right:20px;padding-bottom:20px;padding-left:20px;text-align:left;">
<h1 style="font-family:Helvetica;font-size:26px;font-style:normal;font-weight:bold;line-height:100%;letter-spacing:normal;margin-top:0;margin-right:0;margin-bottom:10px;margin-left:0;text-align:left;color:#202020;">MyAccount - Contact Us</h1>

<strong>AccountType: </strong>Residential
<strong>FirstName: </strong>Test1
<strong>LastName: </strong>Test2
<strong>PhoneNumber: </strong>1111111111
<strong>EmailAddress: </strong>test111@gmail.com
<strong>AccountNumber: </strong>1111111111
<strong>INeedHelpWith: </strong>Start/Stop/Transfer Service Inquiry
<strong>Comments: </strong>We are selling and closing on 10/31/24. Now what?
<strong>Service Address: </strong>111 ABC DR NHP NH 11111

</td>
</tr>

<tr>
<td colspan="3" style="font-family:Helvetica;">

</td>
</tr>
</table>
</td>
</tr>
</table>

</td>
</tr>

</table>
</center>

<div>
The information contained in this e-mail, including any attachment(s), is intended solely for use by the named addressee(s). If you are not the intended recipient, or a person designated as responsible for delivering such messages to the intended recipient, you are not authorized to disclose, copy. Thank you for your cooperation.<br />
</div>

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[binuacs]

@nehashah one way of doing this with regex

[nehashah]

thank you

[nehashah]

HI,

what if i have multiple column and would like to get all data in single row, for example Column1 to Column 7 same information i would like to extract but only column8 would like only those text which is in bold in separate column. 

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10
email_email1	Email1	Test1	<p dir="ltr">Dear ttt r,<br /> <br /> Thank you for your online contact.<br /> <br /> You are welcome.<br /> <br /> It has been my pleasure to assist you.<br /> <br /> If you need to contact us about this inquiry, please respond to this email with your case number <strong>1111111111</strong> in the subject line. <br /> <br /> Sincerely,<br /> Tl<br /> <br /> Customer Service Representative</p>	2024-11-02 23:22:17	Test1	<center> <table border="0" style="height:100%;font-family:Helvetica;margin:0;padding:0;background-color:#d0cccc;border-collapse:collapse;width:100%;"> <tr> <td style="font-family:Helvetica;margin:0;padding:20px;height:100%;width:100%;"> <table border="0" style="font-family:Helvetica;width:750px;border:1px solid #BBBBBB;border-collapse:collapse;"> <tr> <td style="font-family:Helvetica;"> <table border="0" style="font-family:Helvetica;background-color:#FFFFFF;border-bottom:3px solid #ff6e1e;border-collapse:collapse;"> <tr> <td style="padding-top:10px;padding-right:20px;padding-bottom:10px;padding-left:20px;font-family:Helvetica;color:#808080;font-size:10px;line-height:125%;text-align:left;"> </td> </tr> </table> </td> </tr> <tr> <td style="font-family:Helvetica;"> <table border="0" style="font-family:Helvetica;background-color:#FFFFFF;border:20px solid #F4F4F4;border-collapse:collapse;"> <tr> <td colspan="3" style="font-family:Helvetica;color:#505050;font-size:14px;line-height:150%;padding-top:20px;padding-right:20px;padding-bottom:20px;padding-left:20px;text-align:left;"> <h1 style="font-family:Helvetica;font-size:26px;font-style:normal;font-weight:bold;line-height:100%;letter-spacing:normal;margin-top:0;margin-right:0;margin-bottom:10px;margin-left:0;text-align:left;color:#202020;">MyAccount - Contact Us</h1> <strong>AccountType: </strong>Residential <strong>FirstName: </strong>Test1 <strong>LastName: </strong>Test2 <strong>PhoneNumber: </strong>1111111111 <strong>EmailAddress: </strong>test111@gmail.com <strong>AccountNumber: </strong>1111111111 <strong>INeedHelpWith: </strong>Start/Stop/Transfer Service Inquiry <strong>Comments: </strong>We are selling and closing on 10/31/24. Now what? <strong>Service Address: </strong>111 ABC DR NHP NH 11111 </td> </tr> <tr> <td colspan="3" style="font-family:Helvetica;"> </td> </tr> </table> </td> </tr> </table> </td> </tr> </table> </center> <div> The information contained in this e-mail, including any attachment(s), is intended solely for use by the named addressee(s). If you are not the intended recipient, or a person designated as responsible for delivering such messages to the intended recipient, you are not authorized to disclose, copy. Thank you for your cooperation.<br /> </div>	2024-11-02 21:48:53
email-email2	Email1	Test2	<p dir="ltr">Dear ttt r,<br /> <br /> Thank you for your online contact.<br /> <br /> You are welcome.<br /> <br /> It has been my pleasure to assist you.<br /> <br /> If you need to contact us about this inquiry, please respond to this email with your case number <strong>1111111111</strong> in the subject line. <br /> <br /> Sincerely,<br /> Tl<br /> <br /> Customer Service Representative</p>	2024-11-02 23:14:06	Test2	<center> <table border="0" style="height:100%;font-family:Helvetica;margin:0;padding:0;background-color:#d0cccc;border-collapse:collapse;width:100%;"> <tr> <td style="font-family:Helvetica;margin:0;padding:20px;height:100%;width:100%;"> <table border="0" style="font-family:Helvetica;width:750px;border:1px solid #BBBBBB;border-collapse:collapse;"> <tr> <td style="font-family:Helvetica;"> <table border="0" style="font-family:Helvetica;background-color:#FFFFFF;border-bottom:3px solid #ff6e1e;border-collapse:collapse;"> <tr> <td style="padding-top:10px;padding-right:20px;padding-bottom:10px;padding-left:20px;font-family:Helvetica;color:#808080;font-size:10px;line-height:125%;text-align:left;"> </td> </tr> </table> </td> </tr> <tr> <td style="font-family:Helvetica;"> <table border="0" style="font-family:Helvetica;background-color:#FFFFFF;border:20px solid #F4F4F4;border-collapse:collapse;"> <tr> <td colspan="3" style="font-family:Helvetica;color:#505050;font-size:14px;line-height:150%;padding-top:20px;padding-right:20px;padding-bottom:20px;padding-left:20px;text-align:left;"> <h1 style="font-family:Helvetica;font-size:26px;font-style:normal;font-weight:bold;line-height:100%;letter-spacing:normal;margin-top:0;margin-right:0;margin-bottom:10px;margin-left:0;text-align:left;color:#202020;">MyAccount - Contact Us</h1> <strong>AccountType: </strong>Residential <strong>FirstName: </strong>Test1 <strong>LastName: </strong>Test2 <strong>PhoneNumber: </strong>1111111111 <strong>EmailAddress: </strong>test111@gmail.com <strong>AccountNumber: </strong>1111111111 <strong>INeedHelpWith: </strong>Start/Stop/Transfer Service Inquiry <strong>Comments: </strong>We are selling and closing on 10/31/24. Now what? <strong>Service Address: </strong>111 ABC DR NHP NH 11111 </td> </tr> <tr> <td colspan="3" style="font-family:Helvetica;"> </td> </tr> </table> </td> </tr> </table> </td> </tr> </table> </center> <div> The information contained in this e-mail, including any attachment(s), is intended solely for use by the named addressee(s). If you are not the intended recipient, or a person designated as responsible for delivering such messages to the intended recipient, you are not authorized to disclose, copy. Thank you for your cooperation.<br /> </div>	,Start Service,Residential New Service,	2024-10-31 15:04:17

[nehashah]

Hi,

I have attached sample file, this is how data comes in thru email and i would like to separate Column H (Message) start with <Strong> into separate column with column A to G remain on same line. 

thank you for your help.

[binuacs]

@nehashah updated workflow attached

[nehashah]

I couldn't able to open workflow, i saw your screenshot but in that you have assign Record id for each row. Actually i am looking for each row needs to be in separate column for example, first name, email id, with other information remain same only column H (Message) i need some of the information extract into column. 

[binuacs · Accepted solution]

@nehashah 

[nehashah]

thank you so much!