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

p45cal · #3 05-13-2019, 03:30 AM

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.

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