I need to replace #N/A text with some other text in an excel without using for loop

Help Studio
, , ,
1

I used Vlookup for the entire column and some values came as #N/A, I need to replace them with “Yes” text without using loops. Linq or select query is most welcome.

Thanks in advance

image

2

Hi @karthikeyan.srinivasan.ex

Try this

Hope this might help you :slight_smile:

3

Hi @karthikeyan.srinivasan.ex Try below steps

  • Read the excel data to data table
  • Apply below query for data table. Change the column index as required
(From d In  DT.AsEnumerable
Let gp=If(d(0).ToString.equals("#N/A"),"Yes",d(0).ToString)
Select  DT1.Rows.Add(New Object(){gp})).copyToDataTable

Note: Let’s say if there re multiple columns, and if you want to apply this condition to 5th column then above query should change as

(From d In  DT.AsEnumerable
Let gp=If(d(4).ToString.equals("#N/A"),"Yes",d(0).ToString)
Select  DT1.Rows.Add(New Object(){d(0),d(1),d(2),d(3),gp})).copyToDataTable

Check below workflow for more details

SampleProcess.zip (3.0 KB)

Input

Input

Output

output

4

Hi @karthikeyan.srinivasan.ex

Give a try to this

dt.Columns("Requistion").Expression = "IIF([Requistion]='#N/A','Yes','')"

Hope this helps.

1 Like
5

Just modify the replace