Replacing values with a lookup column?

Marco_13112001
Marco_13112001 Member
edited December 2023 in General

Good afternoon,

I'm trying to replace specific values (sample below) from a text field in a dataset, I've highlighted the ones that I was expecting to be changed on this sample; to do that I've created a lookup column with the value and the replacement (see sample below). However, when I a run the Replace Matches function (see below) on my transform step it does not work. Am I missing something on the configuration?

Many thanks in anticipation for your help

Sample text field

"<h3 dir="auto">Why not join The Guildford Hard of Hearing Support Group? We are a friendly active group, who have learnt to live with hearing loss.&nbsp; A hearing aid helps, but if you can lip-read too you will be able to take a more active part in conversations again so enjoying life and family gatherings.</h3><p>The meetings consist of:</p><ul><li>One hour lip-reading class</li><li>A tea interval, when you get to meet other members</li><li>A talk on a topical subject given by an outside speaker</li></ul><h4>Meetings are running on 2nd and 4th Mondays of the month as usual.</h4><h3 dir="auto">&nbsp;</h3><p dir="auto">&nbsp;</p>"

Replace lookup table

image.png

Replace matches function

image.png

Answers

  • Josh Boxer
    Josh Boxer Administrator
    edited August 2023

    Hi Marco

    You selected 'Space-separated', but none of the values you are trying to match are space separated, e.g. '</ul><h4>Meetings' would need to be '</ul> <h4> Meetings' (note the spaces!). An option would be to replace < and > with ' <' and '> ' before your Function, but this would impact other tags like <li>.

    A better option is Regex rather than listing every possible value in a reference file. Something like this:

    image.png 
    RegexReplace(
    
RegexReplace('<h1>abcd</h1><p>defg</p>', '<[hp].>', '<p>', false),
    
'</[hp].>',
    
'</p>',
    
false
    
)

    You could then expand this out to handle more complex cases like '<h3 dir="auto">'

Categories