=LEFT(A1,SEARCH(".",A1)-2)
See if this code line works...aaarrrrrggggghhhhhh.
One of the names has three periods in it. Is it possible to deal with that with this code?
Example
D.J. Kennington D. Kennington
[table="width: 500"]
[tr]
[td]Workbooks(MyWorkbook).Sheets(MyWorksheet).Range("O1").FormulaArray = "=LEFT(R[456]C[-14],MAX(IF(MID(R[456]C[-14],ROW(INDEX(C[-14],1):INDEX(C[-14],LEN(R[456]C[-14]))),1)=""."",ROW(INDEX(C[-14],1):INDEX(C[-14],LEN(R[456]C[-14])))))-3)"[/td]
[/tr]
[/table]
[table="width: 500"]
[tr]
[td]Workbooks(MyWorkbook).Sheets(MyWorksheet).Range("O1").Value = Left(Range("A457").Value, InStrRev(Range("A457").Value, ".") - 3)[/td]
[/tr]
[/table]
Either put select the text an click the button with the # sign or do what the # sign button does and place [/CODE] as the end of the text you want enclosed in the code box and putHow do I get my Code into that format that you have?
IE Code:
and then it goes into that little box.
in front of it. If you wonder why I showed you those in reverse order is because if I put them in correct order, you would not see the codes as this forum's comment processor would have converted them into a code box.
[QUOTE="Brad24, post: 5016661, member: 325210"]I'm playing around with the SPLIT function. I "think" I can read the string into an array and then grab the first two in the array. I don't need the Jr. and that would get me by the names that have D.J. in them
[/QUOTE]
Not necessarily... what if there was (what would be normally) an space between the two initials? Any chance you can post a list of one each of the variances you have in the names text (along with what you want from them) so that we can see all of the varieties you have to work around instead of getting them one at a time like you have been doing?
Ryan Blaney R. Blaney |
#12 |
2 |
Joey Logano J. Logano |
#22 |
3 |
Austin Dillon A. Dillon |
#3 |
4 |
Darrell Wallace Jr. D. Wallace Jr. |
#43 |
5 |
Paul Menard P. Menard |
#21 |
6 |
Michael McDowell M. McDowell |
#34 |
7 |
Denny Hamlin D. Hamlin |
#11 |
8 |
Ryan Newman R. Newman |
#31 |
9 |
Chris Buescher C. Buescher |
#37 |
10 |
Aric Almirola A. Almirola |
#10 |
11 |
Trevor Bayne T. Bayne |
#6 |
12 |
Martin Truex Jr. M. Truex Jr. |
#78 |
13 |
Clint Bowyer C. Bowyer |
#14 |
14 |
Alex Bowman A. Bowman |
#88 |
15 |
AJ Allmendinger A. Allmendinger |
#47 |
16 |
Kurt Busch K. Busch |
#41 |
17 |
Jamie McMurray J. McMurray |
#1 |
18 |
Ricky Stenhouse Jr. R. Stenhouse Jr. |
#17 |
19 |
Kevin Harvick K. Harvick |
#4 |
20 |
Kyle Larson K. Larson |
#42 |
21 |
Kyle Busch K. Busch |
#18 |
22 |
Gray Gaulding G. Gaulding |
#23 |
23 |
Chase Elliott C. Elliott |
#9 |
24 |
Jeffrey Earnhardt J. Earnhardt |
#0 |
25 |
Mark Thompson M. Thompson |
#166 |
26 |
William Byron W. Byron |
#24 |
27 |
D.J. Kennington D. Kennington |
#96 |
28 |
David Ragan D. Ragan |
#38 |
29 |
Matt DiBenedetto M. DiBenedetto |
#32 |
30 |
Brendan Gaughan B. Gaughan |
#162 |
31 |
Erik Jones E. Jones |
#20 |
32 |
Brad Keselowski B. Keselowski |
#2 |
33 |
Daniel Suarez D. Suarez |
#19 |
34 |
Kasey Kahne K. Kahne |
#95 |
35 |
Danica Patrick D. Patrick |
#7 |
36 |
Jimmie Johnson J. Johnson |
#48 |
37 |
Ty Dillon T. Dillon |
#13 |
38 |
Corey LaJoie C. LaJoie |
#72 |
39 |
Justin Marks J. Marks |
#51 |
40 |
David Gilliland D. Gilliland |
#92 |