

LinkBack  Thread Tools  Display Modes 
#1




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 
#2




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; 05132019 at 01:47 AM. 
#3




Quote:
The userdefined one is more robust and a heck of a lot easier to maintain than the other. The builtin 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)) The userdefined function is a lot easier to use: Code:
=blah(D3) 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. Last edited by p45cal; 05132019 at 11:32 AM. 
#4




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) 
#5




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.

#6




Yes, xor's solution is much less resource hungry than mine, doesn't need arrayentering 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. 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Letter Template /w combo box list  etruz  Word  4  10312018 05:16 AM 
Pasting a table with combo boxes to a new document looses the combo box  bobsut@enviroconsult.com  Word  1  01032017 01:29 PM 
Regex/wildcard search for dates with 2digit and 4digit years  Marrick13  Word VBA  2  01292016 07:04 AM 
A challenging digit by digit manipulation, rotate the digit in range of 0 to 9  laucn  Excel Programming  14  05172015 12:12 PM 
Macro to keep first instance and remove duplicates in certain column  zhead  Excel  2  03182015 10:16 AM 