Help expediting Data Entry

Sergio V.

Greetings,

Looking to get some help speeding up our data entry process. We have a note that looks like this: Name: John Doe Phone: 555-555-5555 Email: email@mail.com-and about 15 more fields. These fields need to be taken from the note and manually input in our Smartsheet. 

I would like to know if it's possible to paste a block of text and have Smartsheet categorize each section into the corresponding cell. We had this data entry setup at a previous office that ran with Openoffice, however, I don't know which formulas were being used. We're currently using forms to submit the content, however, the original source is text-based and cannot be changed, meaning the data entry process needs to take place.

If anyone can provide assistance it'll be greatly appreciated.

Andrée Starå

Hi Sergio,

It's probably possible!

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

Is the data always structured as your above example?

Best,

Andrée

Paul Newcome

Andree:

 

I am thinking MID functions using FIND functions for the start and end positions. Is that what you were thinking as well?

Andrée Starå

Paul,

Yes, to parse out the information!

Sergio V.

Thanks for replying Andree! Yes, the text is provided in the same format, it looks something like this:

"Assigned to: John Doe Status: Pending Date: 1/1/2019 Customer: Jane Doe Phone: 555-555-5555 Email: none@mail.com Priority: normal Comments: The quick red fox jumped over the lazy brown dog."

The sheet in question has a column* for each value in bolds. There's 24 values in total. I would share a sheet with you however I'm not sure what our internal policy on sharing outside the company is at the moment.

I'm gonna check the MID and FIND functions as suggested(thanks Paul) but would appreciate any assistance.

Paul Newcome

Is it rows or columns for the values?

.

1. Assigned to              Status               Date

2. Assigned to              Status               Date

or

 

1. Assigned to

2. Status

3. Date

4. Assigned to

5. Status

6. Date

Sergio V.

The values are in the columns, I attached an example. Thanks! 

Paul Newcome

Ok. Sounds good. I am doing some testing and whatnot now. I'll let you know what I come up with. If you're able to find another solution in the meantime though, let me know. Andree can probably come up with something better, but I'm still going to give it a go anyway.

 

 

Paul Newcome

So here goes...

 

I started by creating a helper row. I used row 1 in my example, but you can use whatever row works best for you. I have use the $ to lock in the appropriate row and column references to allow for dragfilling.

 

I named my columns the same as each field and then re-entered the field names across the first row.

.

In the first field column of the first row of actual data, I used this:

=MID($[Text Block]@row, FIND(":", $[Text Block]@row, FIND([Assigned to:]$1, $[Text Block]@row)) + 1, FIND([Status:]$1, $[Text Block]@row) - FIND(":", $[Text Block]@row, FIND([Assigned to:]$1, $[Text Block]@row)) - 1)

.

You can then dragfill this over and down to populate the rest of the fields with the exception of the very last one. For that I used:

=RIGHT($[Text Block]@row, LEN($[Text Block]@row) - FIND(":", $[Text Block]@row, FIND([Comments:]$1, $[Text Block]@row) + 1) - 1)

.

If you were needing to use the date or any fields populated with numbers for further calculations, you will need to convert these fields into usable dates/numbers. Let me know if you need help with that part or if this doesn't work for you.

Paul Newcome

Here's a screenshot... For some reason I kept getting blocked whenever I tried to include it with the solution...