If any cell in any field contains this string, then replace with a numeric value

[mric3]

Hello community!

Drawing a blank on this one. I have multiple columns of survey responses, where respondents can choose any of the following;

Level 1 - Strongly disagree

Level 2

Level 3 - Neither agree nor disagree

Level 4

Level 5 - Strongly agree

For any options throughout the entire survey that have "Level 1 - Strongly disagree", i would like to assign this a value of 1, and so on for the other options, up to a max score of 5.

I tried using the find and replace tool, and the dynamic replace tool, but I'm still having trouble. Goal would be to only have numeric values between 1 and 5 for all responses across all questions.

Any advice?

Thanks! 🙂 

[mric3]

To clarify, i know i can assign a formula for this, but I want to avoid doing this 77 times for each of my 77 questions 🙂 

[Thableaus]

Hi @mric3 

Use an IF condition or a Switch function, that would work.

To apply that to several fields, you might need to use the Multi-Field Formula tool.

Cheers,

[Thableaus]

@mric3 

Ok, since you have multiple questions you might need to use a Lookup table to all these responses and use the Find and Replace Tool.

Do the answers contain the numeric values you want to assign?

Like Level 1 - strongly agree - does it actually contain the "1" number?

Cheers,

[Hadzramin_Rahman]

Hi @mric3 

Hope this helps. This is the result of the attached workflow using Multi-Field Formula.

Workflow

Result

[JessieC]

Nice @Hadzramin_Rahman - much easier 🙂

@mric3 - I came up with this - using Transpose to only have 1 column to parse in RegEx, then just pulling the digit out, and pivoting the data back with a CrossTab

[TonyA]

This may be overkill, but if you want to wipe out everything in the field except the number, why not just use a data cleansing tool with all options selected except removing numbers?

[mric]

Thank you! This did the trick 🙂