Text Parsing in Excel through VBA

Dear sir/madam

I'm wondering is there any way to parse the text in Excel. In a tedious process, this problem is one which is consuming most of the time. I read PDF Form's to Excel files and then process it.

Problem: I'm reading a Text Field from PDF Form using VBA, to an excel cell, where the data from text field looks like this

Please Login or Register  to view this content.

Till now, what I'm doing is, manually copying the required text field and then processing it. On an average I process 70 - 100 files a day.
It's really pain full doing the same thing whole day.

What I look in text is, There will be Some File Name (SRDD_6_2_8_8_1_AHM_ITE_WRN_COND.DOC; ), At the END which will have a WORD "GEN" followed with some number (GEN 97)

Flaws:
1) No standard naming convention,
2) No standard file extention, (In this case all are ".DOC" files, it could be ".H ", " . CD " or any thing for that matter)
3) No limit for number of files (In this case it has 18 files, some time it has 50 to 60 files or even more)

It would be great, if someone knows, if there is a way to automate the process

This is what finally I take up on from the text field

Please Login or Register  to view this content.

If there is some or the other way to do it, please let me know. please sir please.... Your help would lot my life on repeated tasks.

Please let me know if you need more inputs.

Thanks a million in advance

Assuming data in A1

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Changed a bit.

Please Login or Register  to view this content.

Originally Posted by Novice_To_Excel

Woow.... wonder full... this is what I'm looking for so long...

Jindon you saved my life a large extent...

Thank you Jindon Thank You So Much... Thanks to Arlu, Mike & The Forum Admin's...

Unfortunately (for me), I saw this question in the MrExcel forum where the OP posted the question without a link back to this cross-posted question here (someone else posted the link back to here later in thread), so I didn't know the question was already answered here. Anyway, since I developed a solution, and since the solution is different from the one you have (it does not use Regular Expressions, rather, just straight VB coding), I figured I would post it here (assumes your text is in A1 and outputs to B1)...

Please Login or Register  to view this content.

As per your message

Please Login or Register  to view this content.

1) (\S+\.[^ ;/\-]{3,4}) ?
\S+ = Any character(s) other than space

\. = a period

[^ ;/\-]{3,4} = 3 to 4 length of any characters other than space, semi-column, forward slush or hyphen.
It is a Extension part to be checked, so actually {3,4} should be {2,4} as Extension sometimes have 2,3 or 4 characters.

? = with or without space

2) (?:[;/\-]?) ?
(?:[;/\-]?) = Followed or not by semi-column, forward slush or hyphen and with or without a space.

3) (GEN) (\d+)
"GEN" & a space & numbers.

If the string matches above pattern, it can be stored in matches collection via Execute method.
Then you can refer each match in matches collection by its index start from 0.

Please Login or Register  to view this content.

Now use of Replace method, you can refer each sub-matches in each match in match collection reference to the order of open bracket.
But if the bracket begin with (?, it will not referenced, I mean ignored.

So
$1 refer to "(\S+\.[^ ;/\-]{3,4})" part
$2 refer to "(GEN)"
$3 refer to "(\d+)"

Then "$1/$2=$3" will replace the string with
$1 & "/" & $2 & "=" & $3

Understand?

That's one of the beauty part of utilizing Regular Expressions.

Please Login or Register  to view this content.

Pipe means "or".

No problem and don't forget to mark this thread as "Solved"

@ Novice_To_Excel

Welcome to the forum.

Please refrain from using whole post quotes. Rule #12 prohibits them. Also, it seems, based on post #16, that you also broke rule #8 regarding cross posting. Please familiarize yourself with the forum rules.

@Cutter

I have just now gone through the Forum Rules. I promise to adhere to them and please excuse me for inconvenience faced.
Thank you

Try change to

Please Login or Register  to view this content.

@Jindon

Thank you, you have helped me lot, literally save so many hours of my work life. Thanks for the time taken and solving my problem ... Thanks you very much...

Hi

First I need to thank Jindon and the Forum for their great help. You have made my life easy to a large extent...

In continuation with the above Problem and Solution,
I have a doubt... Can we stop the Test Parsing in middle and give the output...

Like

The Text Field contains info like this

Please Login or Register  to view this content.

With the help of Jindon's code, I'm able to get all 5 file names.

Please Login or Register  to view this content.

But, In the above given 5 file names, I would need only first two file names.

Please Login or Register  to view this content.

Here the catch is the File extension will be ".ZIP" and will be followed with a word "CONTAINS", from this line there may me any data which we need to ignore - including the file name with extension .ZIP...

Now the problem is, how to skip the test parsing, when it finds particular pattern like ("FileName.Doc/GEN=5, Contains:......") or word like ("CONTAINS").

And how to ignore these "," which comes in between,
I have tried adding "," in ignore list, but it is still showing

Please Login or Register  to view this content.

Thank you

not sure about "Contains",

Is it just

Please Login or Register  to view this content.

?

Fine I will do that part (Contains) manually, those kind are few per day.

& How to ignore the "," (comma) in the pattern

I have tried with

Please Login or Register  to view this content.

But when I run the code, entire line is being ignored which has " , " (Comma) in between.

Any suggestions....

Post all possible patterns of raw data, otherwise everythnig has to be changed time to time.

Thanks for the patience you have and Sorry for the trouble I gave you... and coming up with new problems added...

That was only part which I'm left behind in "automating text field processing" part

I'm glad with the code you have developed for me, Every thing is fine, except two things
1) Not able to ignore the "," (Comma) which comes in between
2) Need to skip from the line where the File Extension is ".ZIP" and followed with the word "Contains" (It should continue text parsing if ".ZIP" is NOT followed with the word "Contains"

Text Field For Example

Please Login or Register  to view this content.

Required data from the text field

Please Login or Register  to view this content.

Can we make that possible...

Now it needs to evaluate line by line

Please Login or Register  to view this content.

Change to

Please Login or Register  to view this content.

Now the code is doing as we want it, But, when ever there is a number like 10, 20, 30, 40,.... it is resulting ' 0 ', and it is fine in other cases numbers like 6, 27, 59, 123,...

Text Field is Like

Please Login or Register  to view this content.

Result is like

Please Login or Register  to view this content.

Should we need to change section 3 i.e " (\d+) " to accept any number. (Numbers limited to 1 to 10000)

It is working here

result I got

DRFD_6_2_8_8_1_AHM_ITE_WRN_COND.DOC/GEN=10
TGHD_6_2_8_8_2_1_ITE_WARN.DOC/GEN=20
HY_GTM_005_IT_SW_B4.ZIP/GEN=40
HU_YHM_005_IT_SW_B57.DOC/GEN=60
DRFD_6_2_8_8_1_AHM_ITE_WRN_COND.DOC/GEN=12
TGHD_6_2_8_8_2_1_ITE_WARN.DOC/GEN=226

Thanks Jindon

Got the mistake what i have done, and corrected it by looking at your code again. Now it is doing it's part seamlessly...

I understood most most of your code and was able to customize it to a large extent.

But, I think, I'm missing the logic behind these things, may be their might be some pattern or logic in quoting them
[^ ;:/\-,\\]
(?:[ ,;:/\\\-])?
(?:[ ,=])?

However, I grateful to you, You have helped me a lot and made my life easy... Thank you so much...

Now it's time to mark this thread as "Solved".

Thanks Jindon, Marked it as "SOLVED"