Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-02-2020, 10:11 AM
14spar15 14spar15 is offline Extract the last number of the string under certain conditions Windows 7 64bit Extract the last number of the string under certain conditions Office 2010 64bit
Advanced Beginner
Extract the last number of the string under certain conditions
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 10-03-2020, 12:29 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Extract the last number of the string under certain conditions Windows 7 64bit Extract the last number of the string under certain conditions Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Please post a sample sheet
Reply With Quote
  #3  
Old 10-03-2020, 04:58 PM
14spar15 14spar15 is offline Extract the last number of the string under certain conditions Windows 7 64bit Extract the last number of the string under certain conditions Office 2010 64bit
Advanced Beginner
Extract the last number of the string under certain conditions
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

Hello, I assume you're asking for a sample file. I've never done this before but let me try. Thanks
Attached Files
File Type: xlsm Sample1.xlsm (10.0 KB, 8 views)
Reply With Quote
  #4  
Old 10-04-2020, 08:07 AM
p45cal's Avatar
p45cal p45cal is online now Extract the last number of the string under certain conditions Windows 10 Extract the last number of the string under certain conditions Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Attached Files
File Type: xlsx msofficeforums45746Sample1.xlsx (20.2 KB, 6 views)
Reply With Quote
  #5  
Old 10-05-2020, 10:03 AM
14spar15 14spar15 is offline Extract the last number of the string under certain conditions Windows 7 64bit Extract the last number of the string under certain conditions Office 2010 64bit
Advanced Beginner
Extract the last number of the string under certain conditions
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 10-05-2020, 10:52 AM
p45cal's Avatar
p45cal p45cal is online now Extract the last number of the string under certain conditions Windows 10 Extract the last number of the string under certain conditions Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by 14spar15 View Post
Should I be able to add data in column A and in column B right click and refresh?
Yes.

Quote:
Originally Posted by 14spar15 View Post
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
If I'm right that you don't have some version of Power Query (it's an add-in 2010/2013 but built-in in later versions under the Get & Transform section of the Data tab of the ribbon) installed then have a look at these pages:

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract the last number of the string under certain conditions 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 the last number of the string under certain conditions 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:57 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft