#1
|
|||
|
|||
Extract the last number of the string under certain conditions
Hello, I need a formula to extract the last number (will always be a single number) from a string only if that number is preceded by a single letter or two letters and then another number (this second number mentioned will be a group of numbers. All of this is preceded by a group of letters. Example – ABC1234D5 would become ABC1234D, ABC123EF4 would become ABC123EF, ABC12345D would remain ABC12345D and ABC1234 would remain ABC1234. If the process needs to be spread out over a couple columns that’s fine. The original string always starts with one or more letters followed by a group of numbers (2-5 digits). Extracting the last number in the string that is not preceded by another number might also be an option here. The finished results needs to be letters-numbers-letters -or- letters-numbers and never letters-numbers-letters-number. Any help would be appreciated.
|
#2
|
||||
|
||||
Please post a sample sheet
|
#3
|
|||
|
|||
Hello, I assume you're asking for a sample file. I've never done this before but let me try. Thanks
|
#4
|
||||
|
||||
In the attached, in column B is a Power Query offering, just right-click somewhere in that table and choose Refresh.
This is a separate table from the original table in column A, so should you sort the table in column B, the results may no longer line up with the items in column A, even after a refresh. So for the sake of data integrity, there's a second offering in columns E:F. This is a single table so you can sort it however you want safely. It takes its data from column A. |
#5
|
|||
|
|||
Hello, I appreciate the response and the work that you put into this. I’m having problems with this though and I don’t know if I’m doing something wrong. Should I be able to add data in column A and in column B right click and refresh? When I do this I get an error that says “Initialization of data source failed. Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. You see this message again a, create a new data source to correct the database.” I have tried to research this but I can’t figure this out. I am using Excel 2010 if that matters. Thanks
|
#6
|
||||
|
||||
Quote:
Quote:
How To Install Power Query in Excel 2010 | MyExcelOnline How To Install Power Query in Excel 2010 | MyExcelOnline Download Microsoft Power Query for Excel from Official Microsoft Download Center Download Microsoft Power Query for Excel from Official Microsoft Download Center It seems you either need Excel 2010 Professional Plus with Software Assurance (whatever that is) or to upgrade your Excel. However the following site seems to say that Power Query will work in other versions of Excel albeit not officially supported: Power Query Version Compatibility and Installation https://www.myonlinetraininghub.com/...d-installation |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Extract all characters from the nth number of a string | Marcia | Excel | 2 | 02-24-2020 05:15 PM |
Extract States from a String | soldat452002 | Excel | 9 | 04-26-2018 04:16 AM |
Extract a string from a paragraph | kirkm | Word VBA | 7 | 09-11-2016 06:13 PM |
How to extract only numbers from a STRING? | Learner7 | Excel | 3 | 07-02-2013 06:25 AM |
Extract from String using Wildcard | whousedmy | Word | 0 | 05-21-2009 01:35 AM |