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:
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.