How to ignore 2nd delimiter in a data table

I read the email and generate a data table with the delimiter as colon. One of the mails had 2 colons in one of the rows and it didn’t pick details of the entire text, it cut the column based on the 2nd colon and prints only “note” from the below example.

If I try to use a split string - the other rows with just one colon gets affected and it throws “Cannot find column 1” error. How to resolve this?

Client: 1
Rep Note Type: Proposal
Rep Note: note: 2021 qwerrtty

Hi @KavipriyaMoorthy - Could you please post your original string and how to want to split and what you want to write? Sample will help us…

Hi @KavipriyaMoorthy
You can use the regex pattern to replace the double colon with single semicolon

for that use the regex pattern below to replace



Nived N

Happy Automation

@prasath17 - “Rep Note: note: 2021 Tokyo Olympic games airing July 23 to Aug 8 -
contact your rep for avails and booking procedures”

This is the input and “note: 2021 Tokyo Olympic games airing July 23 to Aug 8 -
contact your rep for avails and booking procedures” is the output I want

@KavipriyaMoorthy - Is is what you are looking for??

Two Colons

One Colon

Other way:

str_Text = “Rep Note: note: 2021 Tokyo Olympic games airing July 23 to Aug 8 -
contact your rep for avails and booking procedures”

str_Output = str_Text.Remove(0,instr(str_Text,":"))

str_Output will get value with text after first “:”. You can also remove unnecessary white spaces with:

str_Output = Trim(str_Text.Remove(0,instr(str_Text,":")))

This threw me an error. It is considering the entire text as column 2 and throws an error that column 1 is not found in the part where I am adding it to the dictionary

@KavipriyaMoorthy - Tried the Regex solution??

Yes Prasath, tried the regex. Didn’t work.

@KavipriyaMoorthy -

what you have tried? and what was the error you received? Could you please show us??

Here’s whats happening Prasath. It takes the entire input as a single column instead of dividing rep note as column 1 and rest as column 2


  1. first thing ask to make the format as per requirement for stable RPA.
  2. For your current situation use below logic

Split the original string with : and the merge arrSplitResult(arrsplitResult.length-2) + “:”+ arrSplitResult(arrsplitResult.length-1)

In this way you can get expected output in any numbers of colon before your text

Refer below screenshot…

It reads an entire email and converts it into data table and picks 2nd column values alone. I can’t pick out one row of the mail alone to do the above operation.

Now I didn’t understand your requirement…
Tell me steps from you reading the email…
which activity you are using to read email…and then what is output of read email you are getting …
And what do you mean by convert entire email into data table and pick only second column alone?

The input is an email with values like below:

Firstname: Kavipriya
Secondname: Moorthy
Rep notes: Notes: 2021

I extract the email and generate a data table and using a dictionary, I fill the values to a form.

It uses Gmail so I use Gsuite application scope to get email and take all the values into an excel sheet.

Using read range, I access the values.

I think I got your point…
See if below changes in generate data table works for you…

later you can change the data while adding to the dictionary

1 Like

Ok so @Pravin_Patil1 gave you the right solution. In that case the simplest one is just to check the data in dict before pasting it to form and then split operation or regex will do the trick

I unchecked csv parsing even then it takes the entire value as a column and throws an error. It looks fine in your screenshot, not sure why it isn’t working for me.

Share your screenshot again by pasting same values in the test