Extracting Data from String

[Pranab_C]

Hi All-I need help in extracting "X" and the value that immediately follows it, for instance I would like to extract "X" and "Paid", similarly "X" and "General category" from the example below:-

"(j) X Paid Taxes withheld at source on: foreign Taxes withheld at source on: foreign taxes paid 

or

b Foreign branch category income d X General category income f Certain income re-sourced by treaty"

Your help is truly appreciated.

[davidskaife · Accepted solution]

Hi @Pranab_C 

Will you know before hand what the words are that you need to extract after the X? If that is the case then the attached method may work for you. Essentially have a lookup table with the word combinations you're looking for in and use the Find Replace tool to append it if it finds a match.

If you don't know what the words are then it's going to be a lot trickier, as you've suggested it could be one or two words after the X. How would you define how many words need to be extracted in that case?

To be really pedantic, in the second example the value immediately following the X is just General, not General category..

[binuacs · Accepted solution]

@Pranab_C extract the  2 words after X (that is the pattern), then make the changes according to your requirement