Microsoft Office Forums Remove everything after the last instance of a digit/letter combo
 Register FAQ Search Today's Posts Mark Forums Read

#1
05-12-2019, 02:56 PM
 14spar15 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Mar 2011 Posts: 41
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
05-12-2019, 09:37 PM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,018

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
05-13-2019, 03:30 AM
 p45cal Windows 10 Office 2016 Expert Join Date: Apr 2014 Posts: 253

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
 msOfficeForums42493.xlsm (17.8 KB, 0 views)

Last edited by p45cal; 05-13-2019 at 11:32 AM.
#4
05-13-2019, 04:49 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 436

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.
#5
05-13-2019, 08:07 PM
 14spar15 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Mar 2011 Posts: 41

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
05-14-2019, 05:29 AM
 p45cal Windows 10 Office 2016 Expert Join Date: Apr 2014 Posts: 253

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.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post etruz Word 4 10-31-2018 05:16 AM bobsut@enviro-consult.com Word 1 01-03-2017 01:29 PM Marrick13 Word VBA 2 01-29-2016 07:04 AM laucn Excel Programming 14 05-17-2015 12:12 PM zhead Excel 2 03-18-2015 10:16 AM

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

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top