Hi everybody,
I got programming skills, but never had to deal with VBA. I can read it but I can’t do it from scratch.
I tried a lot of research but failed to find something that was matching my problem.
I understand that my problem must be a classic use case but I couldn’t find the right way to search about it.
My problem:
I got a scanned invoice gone through an OCR, giving me 1200rows for ~500 “codes”.
I need to extract all the “code” in the text of column A.
I got the A column, I want to obtain B column.
<tbody>
</tbody>
link to xls file : http://wikisend.com/download/803406/sample_****ty_data.xlsx
Every A cells :
· Got 0, 1 or more “code”
· Got a space “ “ before the “code” or is the first string in the cell
· Got a space “ “ after the “code” or is the last string in the cell
I can define all “codes” like:
· “ TER?????????? ”
· “ RS??????????? “
· …
· Always 2 or 3 letters + 10 numbers
I can define B like:
FOR EACH cells in A column
IF text in cell contains “TER??????????“ or “RS??????????“ or …
Extract the “code” to first empty cell in B column (if there is 2 or more “code” in the A cell, I need to extract each code in 1 row in B column)
ELSE nothing
How can I extract the B column from A ?
All hints are welcomed (even like: ‘goat, go search that : “problem expressed clearly for coherent search results” )!
Thanks for your attention excel masters,
Hoyoyonono, excel goat.
I got programming skills, but never had to deal with VBA. I can read it but I can’t do it from scratch.
I tried a lot of research but failed to find something that was matching my problem.
I understand that my problem must be a classic use case but I couldn’t find the right way to search about it.
My problem:
I got a scanned invoice gone through an OCR, giving me 1200rows for ~500 “codes”.
I need to extract all the “code” in the text of column A.
I got the A column, I want to obtain B column.
A | B | |
1 | 0 | TER1234567890 |
2 | TER1234567891 | |
3 | sdf sdfsdfsdfsdf | RS1234567890 |
4 | sdf5454sdf TER1234567890 sdfjklslkdfjkj | RS1234567891 |
5 | sdkljfklsjdklfj TER1234567891 | TER1234567899 |
6 | RS1234567890 | … |
7 | ||
8 | dfgdfgdfgdfgdfg | |
9 | RS1234567891 TER1234567899 sdfgdfgdf | |
10 | 0 | |
… | … |
<tbody>
</tbody>
link to xls file : http://wikisend.com/download/803406/sample_****ty_data.xlsx
Every A cells :
· Got 0, 1 or more “code”
· Got a space “ “ before the “code” or is the first string in the cell
· Got a space “ “ after the “code” or is the last string in the cell
I can define all “codes” like:
· “ TER?????????? ”
· “ RS??????????? “
· …
· Always 2 or 3 letters + 10 numbers
I can define B like:
FOR EACH cells in A column
IF text in cell contains “TER??????????“ or “RS??????????“ or …
Extract the “code” to first empty cell in B column (if there is 2 or more “code” in the A cell, I need to extract each code in 1 row in B column)
ELSE nothing
How can I extract the B column from A ?
All hints are welcomed (even like: ‘goat, go search that : “problem expressed clearly for coherent search results” )!
Thanks for your attention excel masters,
Hoyoyonono, excel goat.
Last edited: