Microsoft Office Forums Remove everything after the last instance of a digit/letter combo

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-12-2019, 02:56 PM
14spar15 14spar15 is offline Remove everything after the last instance of a digit/letter combo Windows 7 64bit Remove everything after the last instance of a digit/letter combo Office 2010 64bit
Advanced Beginner
Remove everything after the last instance of a digit/letter combo
 
Join Date: Mar 2011
Posts: 41
14spar15 is on a distinguished road
Default Remove everything after the last instance of a digit/letter combo


Hello, I have strings that I need a formula (preferably) or Macro (less preferred) to remove everything after the last instance of a digit/letter combo. I'm not sure I’m explaining this with the right wording so here's some examples.

Red Black Green 1640 YX234U Front Rear
(Removes after YX234U “Front Rear” leaving "Red Black Green 1640 YX234U")

White Yellow 2233F TX645 Front Rear Side
(Removes after TX645 “Front Rear Side” leaving "White Yellow 2233F TX645")

Thank you
Reply With Quote
  #2  
Old 05-12-2019, 09:37 PM
xor xor is offline Remove everything after the last instance of a digit/letter combo Windows 10 Remove everything after the last instance of a digit/letter combo Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

I guess there is a much better way to do this but until further you might want to try (with your string in A3)

=MID(A3,1,FIND(" ",A3,MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A3 )),FIND({1,2,3,4,5,6,7,8,9,0},A3)))))&" "&MID(A3,FIND(" ",A3,MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A3 )),FIND({1,2,3,4,5,6,7,8,9,0},A3))))+1,FIND(" ",A3,FIND(" ",A3,MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A3 )),FIND({1,2,3,4,5,6,7,8,9,0},A3))))+1)-FIND(" ",A3,MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A3 )),FIND({1,2,3,4,5,6,7,8,9,0},A3)))))

Last edited by xor; 05-13-2019 at 01:47 AM.
Reply With Quote
  #3  
Old 05-13-2019, 03:30 AM
p45cal p45cal is offline Remove everything after the last instance of a digit/letter combo Windows 10 Remove everything after the last instance of a digit/letter combo Office 2016
Expert
 
Join Date: Apr 2014
Posts: 280
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

Quote:
I need a formula (preferably) or Macro (less preferred)
In the attached are 2 offerings, one using only built-in worksheet functions, the other using a user-defined function(which is sort of like a macro).
The user-defined one is more robust and a heck of a lot easier to maintain than the other.
The built-in only formula is long:
Code:
=TRIM(LEFT(D3,SEARCH(" ",D3 & " ",MAX(IF(
(CODE(MID(D3,ROW($A$1:INDEX($A:$A,LEN(D3)-1)),1))>47)*(CODE(MID(D3,ROW($A$1:INDEX($A:$A,LEN(D3)-1)),1))<58)*(CODE(MID(D3,ROW($A$2:INDEX($A:$A,LEN(D3))),1))>64)*(CODE(MID(D3,ROW($A$2:INDEX($A:$A,LEN(D3))),1))<91)+
(CODE(MID(D3,ROW($A$1:INDEX($A:$A,LEN(D3)-1)),1))>64)*(CODE(MID(D3,ROW($A$1:INDEX($A:$A,LEN(D3)-1)),1))<91)*(CODE(MID(D3,ROW($A$2:INDEX($A:$A,LEN(D3))),1))>47)*(CODE(MID(D3,ROW($A$2:INDEX($A:$A,LEN(D3))),1))<58)>0,
ROW($A$1:INDEX($A:$A,LEN(D3)-1)))))-1))
and needs to be array-entered meaning that to commit the formula to the sheet you need to press Ctrl+Shift+Enter, not just Enter. Also, it needs to find upper case letters with numbers (it doesn't find lower-case letters mixed with numbers (this would make the longer (although if you were to change both instances of 91 in the formula to 123 it might be good enough to include lower case letters))). This is in column E of the attached. If there are a lot of such formulae it could become quite resource-hungry, slowing down recalculation time (of course, you can copy/paste..Values to eliminate the formulae afterwars).

The user-defined function is a lot easier to use:
Code:
=blah(D3)
and is case insensitive, much less resource hungry and faster. This is in column F of the attached.


Also in the attached are some of the intermediate steps I took to arrive at the very long formula, as well as column G which compares the results of the two approaches to see if they're the same; these can all be deleted.
Attached Files
File Type: xlsm msOfficeForums42493.xlsm (17.8 KB, 0 views)

Last edited by p45cal; 05-13-2019 at 11:32 AM.
Reply With Quote
  #4  
Old 05-13-2019, 04:49 AM
ArviLaanemets ArviLaanemets is offline Remove everything after the last instance of a digit/letter combo Windows 8 Remove everything after the last instance of a digit/letter combo Office 2016
Expert
 
Join Date: May 2017
Posts: 463
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

When the number of possible variants of beginnings of end part of strings is not more than 6 (seventh IFERROR() is reserved for no ending found), then like this:
Code:
=IFERROR(LEFT($A2,IFERROR(SEARCH(" Front",$A2),IFERROR(SEARCH(" Top",$A2),IFERROR(SEARCH(" Rear",$A2),IFERROR(SEARCH(" Bottom",$A2),0))))-1),$A2)
When there is more possible starts for ending part, then you have to croup IFERROR()'s using IF(). But the formula will be terrible - probably you have to use several helper columns to keep some overview how the calculation is done and which condition is where applied.
Reply With Quote
  #5  
Old 05-13-2019, 08:07 PM
14spar15 14spar15 is offline Remove everything after the last instance of a digit/letter combo Windows 7 64bit Remove everything after the last instance of a digit/letter combo Office 2010 64bit
Advanced Beginner
Remove everything after the last instance of a digit/letter combo
 
Join Date: Mar 2011
Posts: 41
14spar15 is on a distinguished road
Default

Wow!!, This turned out to be much more involved than I first thought. I tried xor’s solution first and it seems to be working pretty good. Reading onto some of the other post I’m seeing more robust, easier to maintain, resource hungry and faster. As soon as I get a chance here I will experiment with some of these other options. Thanks for all the help here.
Reply With Quote
  #6  
Old 05-14-2019, 05:29 AM
p45cal p45cal is offline Remove everything after the last instance of a digit/letter combo Windows 10 Remove everything after the last instance of a digit/letter combo Office 2016
Expert
 
Join Date: Apr 2014
Posts: 280
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

Yes, xor's solution is much less resource hungry than mine, doesn't need array-entering and is shorter too, however be aware, that if there are multiple digit/letter combos in the same string next to each other that they must only be separated by a single space.
Replacing all of the 15 instances of A3 in xor's formula with TRIM(A3) seems to help in quick testing.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Letter Template /w combo box list etruz Word 4 10-31-2018 05:16 AM
Pasting a table with combo boxes to a new document looses the combo box bobsut@enviro-consult.com Word 1 01-03-2017 01:29 PM
Regex/wildcard search for dates with 2-digit and 4-digit years Marrick13 Word VBA 2 01-29-2016 07:04 AM
Remove everything after the last instance of a digit/letter combo A challenging digit by digit manipulation, rotate the digit in range of 0 to 9 laucn Excel Programming 14 05-17-2015 12:12 PM
Macro to keep first instance and remove duplicates in certain column zhead Excel 2 03-18-2015 10:16 AM


All times are GMT -7. The time now is 07:40 PM.


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