How to use extract text and copy

Extract text and copy is an excel file instruction that takes source and target columns and uses a set of rules to extract piece of text from the source and copy it to the column in target. This instruction offers flexibility over complex and nested string formulas in MS Excel. In this article, you will find benefits of this instruction, how to configure this instruction in the process and meaning of the available options. At the end of this article, there is a reference file with the sample text and 111 combinations of the rules that can be used in this instruction.

Why you should use this?

  1. Rules expression are in simple and plain English
  2. Offers great flexibility and ease of use over complex and nested string formulas in Excel
  3. Rules expression in simple and plain English that is easy to understand
  4. Complete audit trail in using instruction to avoid black box syndrome

Example

Below is a sample product number that has meaning coded into. First character of the product number indicates the product class, number represents serial number, characters 5 and 6 represents country code. We want to extract these information from product number to perform further analysis and calculations later.

Product Number Product Class Country Code
M123AU 456 B M AU
P854NZ 345 B P NZ
R234US 234 V R US
W098IN 455 G W IN

1) From : “First character” To : Character at position 1 – This will extract product class. Extract text and copy instruction can extract these information and copy it to specified target range using following 2 rules

2) From : “Character at position 5” to “Character at position 2” – This will extract the country code.

Steps to configure this instruction

Steps are divided into 3 sections (1) Configure excel files, worksheet and range addresses (2) Configure rules and (3) configure tolerance level for bad records

Section-1 ( Source and target files )
  1. Click on Link button in the Source, and select an excel file to work with
  2. Select a worksheet from the drop down list of worksheets
  3. Select an Advanced Range option and configure the range address based on the source data
  4. Click on Link button in the Target, and select an excel file to copy the extracted piece of text
  5. Select a worksheet from the drop down list in the target
  6. Specify the range address in the target based on your requirement
  7. Section-2 ( Rules for text extraction )
  8. In the rules section, click on the ‘From’ drop down list and select “Character at Position…” rule
  9. Enter the character position number.
  10. Click on the ‘To’ drop down list and select “Character at Position…” rule
  11. Enter the character position number.
  12. From and To rules will be different based on what piece of text needs to be extracted. For an example of combinations, please refer to list of different combinations along with example of text at the end of this article.

    Section-3 ( Tolerance level for bad records )
  13. Choose level of tolerance for bad records in the selected source range
  14. Choose the outcome type upon tolerance level is reached.
  15. Click on Save button to save configured step details and then click on Run button to run this step.

If “Fail” option is selected, step will fail upon tolerance level is reached and no text will be copied to target file. If “Issue a warning” option is selected, then step will complete with warning and copy the valid extraction to the target file.

Step will pass successfully and copy the extracted piece of text into specified target, when all values in the specified source range is valid.

List of unique combinations of rules to configure instruction

There are 111 different combinations of From and To rules that can be used to extract the different piece of information from the source data. Below is a list of those combinations along with the sample text.

No Sample Text Rule-From Rule-To Extracted text
1 SVX_Test 055 SYD First Character First Letter S
2 SVX_Test 055 SYD First Character First Number SVX_Test
3 055 SVX First Character First Number 0
4 SVX_Test 055 SYD First Character First Space SVX_Test
5 SVX_Test 055 SYD First Character First Instance of Character…(T) SVX_
6 SVX_Test 055 SYD First Character Character at position…(3) SVX
7 SVX_Test 055 SYD First Character Next Character SV
8 SVX_Test 055 SYD First Character Next letter SV
9 SVX_Test 055 SYD First Character Next number SVX_Test 0
10 SVX_Test 055 SYD First Character Next Space SVX_Test
11 SVX_Test 055 SYD First Character Last letter SVX_Test 055 SYD
12 SVX_Test 055 SYD First Character Last number SVX_Test 055
13 SVX_Test 055 SYD First Character Last space SVX_Test 055
14 SVX_Test 055 SYD First Character Last instance of character…Y SVX_Test 055 SY
15 SVX_Test 055 SYD First Character End of text SVX_Test 055 SYD
16 SVX_Test 055 SYD First Letter First Number SVX_Test
17 SVX_Test 055 SYD First Letter First Space SVX_Test
18 SVX_Test 055 SYD First Letter First Instance of Character…(T) SVX_
19 SVX_Test 055 SYD First Letter Character at position…(3) SVX
20 SVX_Test 055 SYD First Letter Next Character SV
21 SVX_Test 055 SYD First Letter Next letter SV
22 SVX_Test 055 SYD First Letter Next number SVX_Test 0
23 SVX_Test 055 SYD First Letter Next Space SVX_Test
24 SVX_Test 055 SYD First Letter Last letter SVX_Test 055 SYD
25 SVX_Test 055 SYD First Letter Last number SVX_Test 055
26 SVX_Test 055 SYD First Letter Last space SVX_Test 055
27 SVX_Test 055 SYD First Letter Last instance of character…Y SVX_Test 055 SY
28 SVX_Test 055 SYD First Letter End of text SVX_Test 055 SYD
29 SVX_Test 055 SYD First Number First letter 055
30 SVX_Test 055 SYD First Number First Space 055
31 SVX_Test 055 SYD First Number First Instance of Character…(D) 055 SY
32 SVX_Test 055 SYD First Number Character at position…(3) 055
33 SVX_Test 055 SYD First Number Next Character 05
34 SVX_Test 055 SYD First Number Next letter 055 S
35 SVX_Test 055 SYD First Number Next number 05
36 SVX_Test 055 SYD First Number Next Space 055
37 SVX_Test 055 SYD First Number Last letter 055 SYD
38 SVX_Test 055 SYD First Number Last number 055
39 SVX_Test 055 SYD First Number Last space 055
40 SVX_Test 055 SYD First Number Last instance of character…Y 055 SY
41 SVX_Test 055 SYD First Number End of text 055 SYD
42 SVX_Test 055 SYD First Space First Letter  055
43 SVX_Test 055 SYD First Space First Number
44 SVX_Test 055 SYD First Space First Instance of character…(S)  055
45 SVX_Test 055 SYD First Space Character at position…(3)  05
46 SVX_Test 055 SYD First Space Next Character  0
47 SVX_Test 055 SYD First Space Next letter  055 S
48 SVX_Test 055 SYD First Space Next number  0
49 SVX_Test 055 SYD First Space Next Space  055
50 SVX_Test 055 SYD First Space Last letter  055 SYD
51 SVX_Test 055 SYD First Space Last number  055
52 SVX_Test 055 SYD First Space Last space  055
53 SVX_Test 055 SYD First Space Last instance of character…Y  055 SY
54 SVX_Test 055 SYD First Space End of text  055 SYD
55 SVX_Test 055 SYD First instance of Character (0) First letter 055
56 SVX_Test 055 SYD First instance of Character (T) First Number Test
57 SVX_Test 055 SYD First instance of Character (T) First Space Test
58 SVX_Test 055 SYD First instance of Character (T) First instance of character…(Y) Test 055 S
59 SVX_Test 055 SYD First instance of Character (T) Character at position…(4) Test
60 SVX_Test 055 SYD First instance of Character (T) Next Character Te
61 SVX_Test 055 SYD First instance of Character (T) Next letter Te
62 SVX_Test 055 SYD First instance of Character (T) Next number Test 0
63 SVX_Test 055 SYD First instance of Character (T) Next Space Test
64 SVX_Test 055 SYD First instance of Character (T) Last letter Test 055 SYD
65 SVX_Test 055 SYD First instance of Character (T) Last number Test 055
66 SVX_Test 055 SYD First instance of Character (T) Last space Test 055
67 SVX_Test 055 SYD First instance of Character (T) Last instance of character…Y Test 055 SY
68 SVX_Test 055 SYD First instance of Character (T) End of text Test 055 SYD
69 SVX_Test 055 SYD Character at position…(5) First letter T
70 SVX_Test 055 SYD Character at position…(5) First Number Test
71 SVX_Test 055 SYD Character at position…(5) First Space Test
72 SVX_Test 055 SYD Character at position…(5) First instance of character…(Y) Test 055 S
73 SVX_Test 055 SYD Character at position…(5) Character at position…(3) Tes
74 SVX_Test 055 SYD Character at position…(5) Next Character Te
75 SVX_Test 055 SYD Character at position…(5) Next letter Te
76 SVX_Test 055 SYD Character at position…(5) Next number Test 0
77 SVX_Test 055 SYD Character at position…(5) Next Space Test
78 SVX_Test 055 SYD Character at position…(5) Last letter Test 055 SYD
79 SVX_Test 055 SYD Character at position…(5) Last number Test 055
80 SVX_Test 055 SYD Character at position…(5) Last space Test 055
81 SVX_Test 055 SYD Character at position…(5) Last instance of character…Y Test 055 SY
82 SVX_Test 055 SYD Character at position…(5) End of text Test 055 SYD
83 SVX_Test 055 Last letter First instance of character…(5) t 0
84 SVX_Test 055 Last letter Next Character t
85 SVX_Test 055 Last letter Last number t 055
86 SVX_Test 055 Last letter Last space t
87 SVX_Test 0556 Last letter Last instance of character…(6) t 0556
88 SVX_Test 055 Last letter End of text t 055
89 SVX_Test 055 SYD AUS Last Number First instance of character…(Y) 5 S
90 SVX_Test 055 SYD AUS Last Number Next Character 5
91 SVX_Test 055 SYD AUS Last Number Last space 5 SYD
92 SVX_Test 055 SYD AUS Last Number Last instance of character…Y 5 SY
93 SVX_Test 055 SYD AUS Last Number End of text 5 SYD AUS
94 SVX_Test 055 SYD Last space First instance of character…(Y)  S
95 SVX_Test 055 SYD Last space Next character  S
96 SVX_Test 055 SYD Last space Last instance of character…Y  SY
97 SVX_Test 055 SYD Last space End of text  SYD
98 SVX_Test 055 SYD Last instance of character…(0) First Letter 055
99 SVX_Test 055 SYD Last instance of character…(X) First Number X_Test
100 SVX_Test 055 SYD Last instance of character…(X) First Space X_Test
101 SVX_Test 055 SYD Last instance of character…(T) First Instance of Character…(D) t 055 SY
102 SVX_Test 055 SYD Last instance of character…(X) Character at position…(3) X_T
103 SVX_Test 055 SYD Last instance of character…(X) Next Character X_
104 SVX_Test 055 SYD Last instance of character…(X) Next letter X_T
105 SVX_Test 055 SYD Last instance of character…(X) Next number X_Test 0
106 SVX_Test 055 SYD Last instance of character…(X) Next Space X_Test
107 SVX_Test 055 SYD Last instance of character…(X) Last letter X_Test 055 SYD
108 SVX_Test 055 SYD Last instance of character…(X) Last number X_Test 055
109 SVX_Test 055 SYD Last instance of character…(X) Last space X_Test 055
110 SVX_Test 055 SYD Last instance of character…(X) Last instance of character…Y X_Test 055 SY
111 SVX_Test 055 SYD Last instance of character…(X) End of text X_Test 055 SYD
Leave a Reply

Latest Tweets

Free eBook: 10 Tips for sourcing good data yucalc.com/2017/02/17/fre…

reply · retweet · favorite

Newsletter

By signing up, you agree to our Terms of Service and Privacy Policy.

Address

Suite 1506, Westfield Tower 2, 101 Grafton St Bondi Junction NSW 2022 Australia

View Larger Map

Contact Us

AUS: +61-2-9386-0202
UK: +44-20-8123-1251
Send us an email

ContactUs.com
WordPress Image Lightbox