Finding a specific value embedded in a text string in Excel

Question

Is there a way to find a specific value in a text string in Excel and return another value in that string if found?

Ex.

Total Therm    2TC92998 11/16-12/16  30     93021.0000     93336.0000       315.0000     1.0000 1.00000      315.0000 A

The value "2TC92998" in the above text string represents a fixed meter ID in an invoice received as PDF and converted to Excel.

The value in the penultimate position in the text string (315.0000) is the consumption amount desired to be extracted into a separate field.

The end result desired is:

METER #	THERMS
2TC92998	315.0

There are hundreds of these text strings distributed throughout the converted invoice, so using "Text to Columns" does not seem feasible.

Any known solutions using Excel functions?

Thanks.

Answer 1

re: "Exactly how are you converting the PDF to Excel?"

Using built-in "Export PDF" from Adobe Acrobat Pro Tools menu.

Cool. I don't use Acrobat so I didn't know it could do that.

.

So PowerQuery is still an option, maybe a better option, but this opens up another alternative.

.

In excel try using the Data tab > Data Tools group > Text to Columns command. Try using the "Delimited" option. Here is what is in the "next" step, step 2

If that doesn't work, then I would strongly suggest going to PowerQuery. With a little trial and error it can be easier to do from scratch than VBA. PQ will allow you to delete all of the unwanted data as part of the process of splitting. Much of what you can do in PowerQuery can be done from the ribbon or context menus. PQ records actions you initiate in the ribbon as discrete steps, so you can break a complex task into small, easy steps. PQ actually generates code that is hidden, but accessible. Like a macro, you can re-run the query you generate as new data comes in. You can also go back and edit the query if you need to.

.

Upload Example - Share via “Personal” OneDrive File

There is no way to upload example files directly to this forum.
.

Trouble shooting problems using this text only forum can be like a visit to the dentist without anaesthetics: a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we hopefully can eventually come up with a / “the” solution.
.

Often it is faster and easier for everyone if we have a “sample file(s)” get "hands on", to look at, and to “play with”. There are a couple of advantages of providing example files:

. * we have exactly the same data you are describing,

. * if your problem requires merging multiple files, we can work with that

. * you will often get different suggestions from users with points of view and experience.
.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:
https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471?tm=1523189328156
.

The above article includes links to a macros to randomize existing text in Word and Excel
.

Note: make sure to upload to your Personal (consumer) OneDrive rather than your work OneDrive or SharePoint. Files in the Work cloud can be hard to share due to security restrictions placed by the work admins. Or use any other free storage service (anything other than business OneDrive or SharePoint).
.

Another article explaining need for sharing example files:

Why a sample file is important for troubleshooting. How to do it. https://answers.microsoft.com/en-us/msoffice/forum/all/why-a-sample-file-is-important-for-troubleshooting/9441ae3c-1e92-41c6-9a1f-5b377b08e5a5
.

2021 05 25- 15 Best Free Cloud Storage in 2021 – Up to 200 GB Free Storage
https://www.whizlabs.com/blog/best-free-cloud-storage/
.

.

Answer 2

Re: "Yes - adding a helper column(s) is okay. Open to vba code as well."

If this doesn't suit, I will work on the vba code...

(click image to enlarge)

=IF(COUNTIN(A3,$B$1)>0,NUMSRIGHT(A3)*1,"")

The CountIn and NumsRight functions are part of the free 'Custom_Functions' Excel add-in. The add-in contains 20+ new Excel functions that work exactly like the built-in functions. Download from OneDrive... https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU There is also a Word.docx examples file available there.

'Nothing Left to Lose

Answer 3

I'll try it and let you know results, thanks for the quick response!

Answer 4

Nothing Left to Lose,

A typical monthly PDF invoice results in >2650 rows when converted to Excel, 144 of which contain the known Meter IDs described above. The text strings always start in Column A, but spacing is random between the target 144 rows.

I tried VLOOKUP in a separate sheet against the known Meter ID list, it works to return the THERM amount but only if "Text to Columns" is applied manually to all of the 144 rows. Too much time for that.

Yes - adding a helper column(s) is okay. Open to vba code as well.

Thank you!

Answer 5

re: "distributed throughout the converted invoice"

Are they all in the same column?
Which one?

Is adding an additional column (helper column) OK?

Are you willing to use vba code?

'---
Nothing Left to Lose