Process design - Excel data manipulation


I have a scenario where I use OCR to extract data from a PDF file into a results excel file. I have to manipulate two of the columns of data as detailed below. What is the best practice to achieve this?

  1. Create a new column based on a value in column A based on data from a separate excel file. For example, if cell A1 has a value “Red” the system would need to look for the value in a separate excel file and enter the value found “100” as an example.

  2. Add (-) and (0) in between characters in column B. For example, cell B1 has a value of 777888999. I need to manipulate this value to read 777-888-999-00.

Any suggestions to achieve this would be greatly appreciated.

Thank you,

Hello @MF.RPA, for the first one, you can perform a lookup and fetch the value accordingly but before that, use an if condition to see if the value you are searching for (Red, for example), exists in the column and based on the result, fetch the value.

For the second one, you can use the insert() method to insert the hypens and zeros. I’m suggesting this hoping that your string always contains 9 characters.

Try it out and let me know in case of any issues.

1 Like


Thank you for the suggestion. I will try this today and report back.

1 Like