#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; 05-13-2019 at 01:47 AM. |
#3
|
||||
|
||||
Quote:
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)) The user-defined 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; 05-13-2019 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 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. |
|
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 |
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 |