Trouble Extracting A String

Brad24

Board Regular
Joined
May 4, 2015
Messages
81
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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)"
 
Upvote 0
I got it! Thanks for the help.
Solution was to put this:
=LEFT(A457,SEARCH(" & Chr(34) & Chr(46) & Chr(34) & ", A457)-2)"
 
Upvote 0
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
 
Upvote 0
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...
Code:
[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...
Code:
[table="width: 500"]
[tr]
	[td]Workbooks(MyWorkbook).Sheets(MyWorksheet).Range("O1").Value = Left(Range("A457").Value, InStrRev(Range("A457").Value, ".") - 3)[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
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
Code:
 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?
 
Last edited:
Upvote 0
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]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top