Help expediting Data Entry

Help expediting Data Entry

edited 12/09/19 in Using Smartsheet

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 protected] 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.

Comments

  • Andrée Starå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, [email protected])

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Is the data always structured as your above example?

    Best,

    Andrée

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul 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åAndrée Starå ✭✭✭✭✭

    Paul,

    Yes, to parse out the information!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • edited 06/14/19

    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[email protected] 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 NewcomePaul 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

  • edited 06/14/19

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

    Screen Shot 2019-06-14 at 8.19.21 PM.png

  • Paul NewcomePaul 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 NewcomePaul 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 NewcomePaul Newcome ✭✭✭✭✭

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

    Comm.PNG

Sign In or Register to comment.