How to use excel functions in uipath

[kumari_supriya]

hi all,

i want to use excel formulas in uipath.
not getting how to use.
can anyone suggest.
like below formulas
LEFT(A1,(FIND(" “,A1,1)-1))
MID(A1,FIND(” ",A1)+1,256)
i tried with write cell “=CONCATENATE(B2,” “,C2)”,but not working

[jitendra_123 (Jitendra Ughade)]

Hi @kumari_supriya
You Can Use Write Cell to Insert In the Column the Use Auto fill range Activity to use that formula. You just have to give the Range From where you will get the formula.

[Palaniyappan (Forum Leader)]

Hi @kumari_supriya

We can use write cell activity to mention the formula in the excel
— use a excel application scope activity and pass the file path as input
— use write cell activity and mention the value as
“=CONCATENATE(B2,” “,C2)”
— mention the cell position between double quotes

Hope this would help you
Cheers

[Anmol_Yadav]

Try using write cell activity inside excel application scope or write cell activity of a workbook.

[kumari_supriya]

Hi Palaniyappan,

1.i used within excel application scope

used write cell activity
after running i2 value is showing
which is not my required value.
kindly help

[samir (Samir Mohite)]

Hi @kumari_supriya

put this in your write cell activity → “=CONCATENATE(B2,”" “”,C2)"
when we write quotes in write cell we’ve to mention it twice

[Palaniyappan (Forum Leader)]

You were almost done
It should be like this in write cell
=CONCATENATE(B2, “”" "””, C2)

A space next to , and double quotes surrounded by double quotes
#NAME? usually means there are quotation marks missing from a Text argument.

Cheers @kumari_supriya

[kumari_supriya]

hi @Palaniyappan

Thanks a lot it worked.
But can you tell me how to use vlookup one in write cell.
i used “=VLOOKUP(A2,EXTRACT_AD_V11_20190621.csv!$B:$C,2,0)” in write cell.
it is not showing the correct result,instead its showing #N/A
Is there any other way to do?
and once we do write cell for 1st cell how to do for whole column means drag.

Thanks for your help.

[Palaniyappan (Forum Leader)]

@kumari_supriya

it is not showing the correct result,instead its showing #N/A
Is there any other way to do?

Fine that means your vlookup is correct but there is no value for the condition passed
Kindly check with the condition been applied and the output you want from

For this

@kumari_supriya

and once we do write cell for 1st cell how to do for whole column means drag.

Hope this could help you

How to stretch a formula

Hi, There are a couple ways to solve this, but this what I would do… First, you need to use the Select Range activity to select all cells where you want the formula. One way to find the last row you would need to probably use a Data Table from the sheet and use dt.Rows.Count to find the last row. Range would end up being something like this: “A2:C”+(dt.Rows.Count+1).ToString Then, you need to perform the Filldown key combination which is “Ctrl+d”. You can do this using TypeInto “[d(ctrl)]d[u(…

Kindly Try this and let know for any queries or clarification
Cheers @kumari_supriya

[kumari_supriya]

Thanks a lot @Palaniyappan
Now all good.
But It worked fine when i tried to do write range for the whole external workbook sheet to my current excel application scope sheet2 and do vlookup.
is there any other way to do vlookup from external workbook?
and one more query after that i used filter data table activity to filter the data .
but i faced some problem it is not giving the correct data.
i wanted to filter the data on the same column with 2 values.
1st value is showing correct result but not giving result for 2nd values.
attached the screenshot .

result is showing correct for enabled one not for #N/A value.
kindly help me out

Thanks a lot for your help.

[Palaniyappan (Forum Leader)]

Great
This one looks good

@kumari_supriya

But It worked fine when i tried to do write range for the whole external workbook sheet to my current excel application scope sheet2 and do vlookup.

While for this

@kumari_supriya

and one more query after that i used filter data table activity to filter the data .
but i faced some problem it is not giving the correct data.
i wanted to filter the data on the same column with 2 values.
1st value is showing correct result but not giving result for 2nd values.

I hope the cell with value as na will be blank while reading from datatable
So let’s confirm that once
—use a output datatable activity and pass the input as Dt_Unfiltered and Get the output with a variable of type string named out_text
—use a write line activity and pass that out_text activity and check for the value of datatable whether we are getting na or some other term for na in output panel
—based on that keep the value in the filter wizard condition

You were almost done
Hope this would help you
Kindly try this and let know for any queries or clarification
Cheers @kumari_supriya

[kumari_supriya]

@Palaniyappan

Yes you are correct.
When i did the writeline for the data table ,i saw #N/A value is showing as -2146826246.
Hence applied filter now for -2146826246 this value .
so in the output correct rows are displayed now.
But for the user _status column #N/A value are now displayed as -2146826246.

Thanks for your help.

[Palaniyappan (Forum Leader)]

Actually
-2146826246 is the code for #N/A
For more details on it kindly have a view on this

C# Interop.Excel '#N/A' Cell Value converted to -2146826246

We need to check only why this #N/A comes but I hope this comes only when there’s is no match for the look up we have made
So it won’t be an issue I guess
Hope we are going good

Cheers @kumari_supriya

[Caithlyn_Saturos]

Hi,
Is there a way to concatenate the values through the column names?