Trouble Extracting A String

[Brad24]

Hi, is there an easy way to extract a name from the following string?

The string has the following format. I just want James Bond out of this string.

James Bond J. Bond

 

[Vbagreenhorn1]

try this:

=LEFT(A1,SEARCH(".",A1)-2)

 

[Brad24]

Thanks. I will give it a try

 

[Brad24]

I am getting an error trying to transfer it over to my code. It doesn't seem to like the "." so I changed it to this and it still says the code is wrong. My code is setting a formula

Workbooks(MyWorkbook).Sheets(MyWorksheet).Range("O1").Formula="=LEFT(A457, SEARCH(" & Chr(46) & ", A457) -2)"

 

[Brad24]

I got it! Thanks for the help.
Solution was to put this:
=LEFT(A457,SEARCH(" & Chr(34) & Chr(46) & Chr(34) & ", A457)-2)"

 

[Brad24]

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

 

[Rick Rothstein]

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

See if this code line works...

[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]

I am just wondering though... since you are using VB code, do you really need this code line to put a "live" formula in the cell... are you really expecting the value in cell A457 to be changed manually sometime after the formula has been placed such that you need a "live" formula in cell O1? If just placing the answer in the cell directly is acceptable, then you could use this code line to put the name in cell O1 directly...

[table="width: 500"]
[tr]
	[td]Workbooks(MyWorkbook).Sheets(MyWorksheet).Range("O1").Value = Left(Range("A457").Value, InStrRev(Range("A457").Value, ".") - 3)[/td]
[/tr]
[/table]

 

[Brad24]

Thanks Rick, that works but I have another issue that killed it. I was using some other code I have here that had the .Formula in it, but you are correct, I didn't need that in this case, so I will change it.

How do I get my Code into that format that you have?
IE Code:
and then it goes into that little box.

I have another name that this fails on: Ricky Stenhouse Jr. R. Stenhouse

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

 

[Rick Rothstein]

How do I get my Code into that format that you have?
IE Code:
and then it goes into that little box.

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 put

 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?

 

[Brad24]

Here is the list. I wasn't thinking ahead and didn't realize I needed to take into account the different variances. That got me there. I don't believe there will be any other variances. They are all in column A. The 3 rows for each guy are just 1 -40(40 drivers), than their name which I am trying to pull out, and then their car number, which I am also trying to pull out.

So from this, I need name and car number. Thought it was going to be easier than I have found as I had some similar help last weekend on something similar but just different enough that I can't figure it out.
1
[TABLE="width: 214"]
<tbody>[TR]
[TD]Ryan Blaney R. Blaney[/TD]
[/TR]
[TR]
[TD]#12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]Joey Logano J. Logano[/TD]
[/TR]
[TR]
[TD]#22[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]Austin Dillon A. Dillon[/TD]
[/TR]
[TR]
[TD]#3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
[TR]
[TD]Darrell Wallace Jr. D. Wallace Jr.[/TD]
[/TR]
[TR]
[TD]#43[/TD]
[/TR]
[TR]
[TD]5[/TD]
[/TR]
[TR]
[TD]Paul Menard P. Menard[/TD]
[/TR]
[TR]
[TD]#21[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]Michael McDowell M. McDowell[/TD]
[/TR]
[TR]
[TD]#34[/TD]
[/TR]
[TR]
[TD]7[/TD]
[/TR]
[TR]
[TD]Denny Hamlin D. Hamlin[/TD]
[/TR]
[TR]
[TD]#11[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]Ryan Newman R. Newman[/TD]
[/TR]
[TR]
[TD]#31[/TD]
[/TR]
[TR]
[TD]9[/TD]
[/TR]
[TR]
[TD]Chris Buescher C. Buescher[/TD]
[/TR]
[TR]
[TD]#37[/TD]
[/TR]
[TR]
[TD]10[/TD]
[/TR]
[TR]
[TD]Aric Almirola A. Almirola[/TD]
[/TR]
[TR]
[TD]#10[/TD]
[/TR]
[TR]
[TD]11[/TD]
[/TR]
[TR]
[TD]Trevor Bayne T. Bayne[/TD]
[/TR]
[TR]
[TD]#6[/TD]
[/TR]
[TR]
[TD]12[/TD]
[/TR]
[TR]
[TD]Martin Truex Jr. M. Truex Jr.[/TD]
[/TR]
[TR]
[TD]#78[/TD]
[/TR]
[TR]
[TD]13[/TD]
[/TR]
[TR]
[TD]Clint Bowyer C. Bowyer[/TD]
[/TR]
[TR]
[TD]#14[/TD]
[/TR]
[TR]
[TD]14[/TD]
[/TR]
[TR]
[TD]Alex Bowman A. Bowman[/TD]
[/TR]
[TR]
[TD]#88[/TD]
[/TR]
[TR]
[TD]15[/TD]
[/TR]
[TR]
[TD]AJ Allmendinger A. Allmendinger[/TD]
[/TR]
[TR]
[TD]#47[/TD]
[/TR]
[TR]
[TD]16[/TD]
[/TR]
[TR]
[TD]Kurt Busch K. Busch[/TD]
[/TR]
[TR]
[TD]#41[/TD]
[/TR]
[TR]
[TD]17[/TD]
[/TR]
[TR]
[TD]Jamie McMurray J. McMurray[/TD]
[/TR]
[TR]
[TD]#1[/TD]
[/TR]
[TR]
[TD]18[/TD]
[/TR]
[TR]
[TD]Ricky Stenhouse Jr. R. Stenhouse Jr.[/TD]
[/TR]
[TR]
[TD]#17[/TD]
[/TR]
[TR]
[TD]19[/TD]
[/TR]
[TR]
[TD]Kevin Harvick K. Harvick[/TD]
[/TR]
[TR]
[TD]#4[/TD]
[/TR]
[TR]
[TD]20[/TD]
[/TR]
[TR]
[TD]Kyle Larson K. Larson[/TD]
[/TR]
[TR]
[TD]#42[/TD]
[/TR]
[TR]
[TD]21[/TD]
[/TR]
[TR]
[TD]Kyle Busch K. Busch[/TD]
[/TR]
[TR]
[TD]#18[/TD]
[/TR]
[TR]
[TD]22[/TD]
[/TR]
[TR]
[TD]Gray Gaulding G. Gaulding[/TD]
[/TR]
[TR]
[TD]#23[/TD]
[/TR]
[TR]
[TD]23[/TD]
[/TR]
[TR]
[TD]Chase Elliott C. Elliott[/TD]
[/TR]
[TR]
[TD]#9[/TD]
[/TR]
[TR]
[TD]24[/TD]
[/TR]
[TR]
[TD]Jeffrey Earnhardt J. Earnhardt[/TD]
[/TR]
[TR]
[TD]#0[/TD]
[/TR]
[TR]
[TD]25[/TD]
[/TR]
[TR]
[TD]Mark Thompson M. Thompson[/TD]
[/TR]
[TR]
[TD]#166[/TD]
[/TR]
[TR]
[TD]26[/TD]
[/TR]
[TR]
[TD]William Byron W. Byron[/TD]
[/TR]
[TR]
[TD]#24[/TD]
[/TR]
[TR]
[TD]27[/TD]
[/TR]
[TR]
[TD]D.J. Kennington D. Kennington[/TD]
[/TR]
[TR]
[TD]#96[/TD]
[/TR]
[TR]
[TD]28[/TD]
[/TR]
[TR]
[TD]David Ragan D. Ragan[/TD]
[/TR]
[TR]
[TD]#38[/TD]
[/TR]
[TR]
[TD]29[/TD]
[/TR]
[TR]
[TD]Matt DiBenedetto M. DiBenedetto[/TD]
[/TR]
[TR]
[TD]#32[/TD]
[/TR]
[TR]
[TD]30[/TD]
[/TR]
[TR]
[TD]Brendan Gaughan B. Gaughan[/TD]
[/TR]
[TR]
[TD]#162[/TD]
[/TR]
[TR]
[TD]31[/TD]
[/TR]
[TR]
[TD]Erik Jones E. Jones[/TD]
[/TR]
[TR]
[TD]#20[/TD]
[/TR]
[TR]
[TD]32[/TD]
[/TR]
[TR]
[TD]Brad Keselowski B. Keselowski[/TD]
[/TR]
[TR]
[TD]#2[/TD]
[/TR]
[TR]
[TD]33[/TD]
[/TR]
[TR]
[TD]Daniel Suarez D. Suarez[/TD]
[/TR]
[TR]
[TD]#19[/TD]
[/TR]
[TR]
[TD]34[/TD]
[/TR]
[TR]
[TD]Kasey Kahne K. Kahne[/TD]
[/TR]
[TR]
[TD]#95[/TD]
[/TR]
[TR]
[TD]35[/TD]
[/TR]
[TR]
[TD]Danica Patrick D. Patrick[/TD]
[/TR]
[TR]
[TD]#7[/TD]
[/TR]
[TR]
[TD]36[/TD]
[/TR]
[TR]
[TD]Jimmie Johnson J. Johnson[/TD]
[/TR]
[TR]
[TD]#48[/TD]
[/TR]
[TR]
[TD]37[/TD]
[/TR]
[TR]
[TD]Ty Dillon T. Dillon[/TD]
[/TR]
[TR]
[TD]#13[/TD]
[/TR]
[TR]
[TD]38[/TD]
[/TR]
[TR]
[TD]Corey LaJoie C. LaJoie[/TD]
[/TR]
[TR]
[TD]#72[/TD]
[/TR]
[TR]
[TD]39[/TD]
[/TR]
[TR]
[TD]Justin Marks J. Marks[/TD]
[/TR]
[TR]
[TD]#51[/TD]
[/TR]
[TR]
[TD]40[/TD]
[/TR]
[TR]
[TD]David Gilliland D. Gilliland[/TD]
[/TR]
[TR]
[TD]#92[/TD]
[/TR]
</tbody>[/TABLE]