How to remove text before " CTC -"...using substring

Help
, , ,
1

Hello All,

So far i am trying this solve this issue… but nothing helps me… I have a column with 200 rows with abbrivation text… eg TTF — Turn Tuple Front i am extracting it from pdf and saving it in excel … but while saving there is some extra text is there so i want to remove that one… below is the same file waht i have right now…
image

so i need it like this…

image

Thanks,
Suresh.

2

Hi can you share the excel file.

3

Hi @vishal.kp, Thanks for your response…

Below is my excel file…

sample_file.xlsx (9.5 KB)

Thanks,
Suresh.

4

Update using For Each activity.
Use Read Range activity to read the data from excel. Output will be saved to datatable DT.

For Each row in DT
   Assign Activity, strValue = row("ColumnName")
   //get the index of hiphen. If it is not hiphen get the index of space
   Assign Activity, intIndex = strValue.IndexOf(" ")
   //if index is greater than 4, then there is some value before TTF (assuming all are three letter abbreviations) 
   //remove the extra value and update the row value
   if intIndex > 4 Then
      Assign Activity, strUpdatedValue = strValue.SubString(intIndex-4)
      Assign Activity, row("ColumnName") = strUpdatedValue
   End If
End For

Use Write Range Activity to write the updated datatable to excel.

Regards,
Karthik Byggari

5

Hello ,
I have created the workflow, refer it @suresh_kumar4
Suresh.xaml (6.3 KB)

6

Hello @KarthikByggari, Thanks for the response…

I am getting the error please correct me if i am wrong… i am sending you the file…

Main.xaml (9.5 KB)

image
image817×633 16.3 KB

image
image719×645 14.8 KB

Thanks,
Suresh.

7

Try, str_input = row(0).ToString

8

Hi @suresh_kumar4 ,

sample_file.xlsx (11.4 KB) RemoveUnwantedText.xaml (7.1 KB)

Please have a look.

Thanks!

9

Thanks,

But Sorry, i am not getting the exact output.

Regards,
Suresh.

10

Where does it go wrong?

11

Thanks a lot i got it…

1 Like
12

Thanks a lot… @kadiravan_kalidoss

13

Hello @kadiravan_kalidoss.,

I used this Regex.Match(row(0).ToString.Trim, “\w+(\s|)—.*”).Value.trim i got the answer what i expect…but in some places there are two “—” lines like this… so i want to trim text before second hypen…

eg: TIRE INFLATION SYSTEM LAMP INDICATOR—LED RTD— Role Tried Divert

I dont want this part TIRE INFLATION SYSTEM LAMP INDICATOR—LED

i need only this part RTD— Role Tried Divert

Thanks,
Suresh.

14

Please confirm all abbreviations are having only 3 characters(like RTD) or it may change?
@suresh_kumar4

15

No it may differ from page to page its like 3 charecters, 2,1,

eg:ATA, RTD, ET, T,…

Thanks,
Suresh.

16

Hi @suresh_kumar4,

Try this,

Regex.Match(row(0).ToString.Trim, “[A-Z]{1,3}(\s|)—([A-Z][a-z]+| )*$”).Value.trim

Thanks!

17

Hi @kadiravan_kalidoss,

Thanks for the response… here is the small issue…

i am getting the data what i expect…

f — front
u — under
lsf— left side front

image

i am getting the data if there is a space f — front and i am not getting the data if there is no space
lsf— left side front

so i need the text if there is space or without space all to be extractted …can you modify the code and send me… to this same expression Regex.Match(row(0).ToString.Trim, “[A-Z]{1,3}(\s|)—([A-Z][a-z]+| )*$”).Value.trim

Thanks,
Suresh.

18

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.