Is it possible to auto-format the information received from pulldata()?

[ChrisSmith2]

For my survey one of the fields I need is Route.

In the spreadsheet I'm pulling the data from it's formatted as shown below. 

031K0017700
064U0005600

059I0013500

059K0000400

The way it needs to appear in the results is: (Letter, 3 numbers)

The examples above would need to end up: K177, U056, I135, & K004

Initially I used a new column and formula* to make the changes before I brought the .csv into Survey123 but in the future the csv will periodically be replaced with a new one (new sample information, same column names) and I was hoping it could just be brought over without additional spreadsheet work before it's brought into Survey123.

Is there there something I can put in the calculation or inputMask field to do the formatting for me?

*Excel formula =RIGHT(LEFT(A1,4),1)&(LEFT(RIGHT(A1,5),3))

[BrandonArmstrong · Accepted solution]

Hi Chris,

I believe that you can accomplish your desired result by placing a 'substr' formula in the calculation column of the desired question within your survey. 

In your example, say I wanted to return K177 from your initial value of 031K0017700.

I would place a 'note' question underneath the question where I am selecting 031K0017700, and in the calculation column, I would place the following...

substr(${question_1},3, 4) + substr(${question_1},6, 9)

This would return the 4th value (K - counting starts at 0) and concatenate it with values 6-8 (177) to give me an overall value of K177 in the note field.  You can take a look an example of a substr formula by clicking on the link below and navigating to the 'non mathematical functions' section.

Formula Operators

I have also attached an example XLSForm to this post

Brandon

[ChrisSmith2]

That works perfectly.  Thanks for your assistance!