Excel Right or Mid Function between a comma and a number

andybord

New Member
Joined
Dec 31, 2013
Messages
9
Hi-

I've searched for an exact solution to this but am coming up slightly short. I have cells with the following data:

C2=Duke 82, Kentucky 76

I want to extract Kentucky in separate tab/cell. The number of characters extracted needs to be variable too.

I'm sure it's a mid formula, but haven't been able to get one to work.

Thanks in advance for the help.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Try this:

=left(mid(a1,find(",",a1)+2,find(",",a1)+2),find(" ",mid(a1,find(",",a1)+2,find(",",a1)+2)))

I like that Trim - replace - search combination and with the left function it will remove the numbers at the end.
Both formulas work.

=LEFT(TRIM(REPLACE(A1,1,SEARCH(",",A1),"")),FIND(" ",TRIM(REPLACE(A1,1,SEARCH(",",A1),""))))

 
Last edited:
Upvote 0
And one more formula for you to consider...

=TRIM(MID(C2,FIND(",",C2)+1,999))

And if the space after the comma is guaranteed to always be there, the above formula can be simplified to this...

=MID(C2,FIND(",",C2)+2,999)
 
Last edited:
Upvote 0
andybord,

Welcome to the MrExcel forum.

Based on your one example, how about this?


Excel 2007
CD
1
2Duke 82, Kentucky 76Kentucky
3
Sheet1
Cell Formulas
RangeFormula
D2=TRIM(MID(SUBSTITUTE(C2," ",REPT(" ",100)),200,100))
 
Last edited:
Upvote 0
Try this:

=left(mid(a1,find(",",a1)+2,find(",",a1)+2),find(" ",mid(a1,find(",",a1)+2,find(",",a1)+2)))


Thanks! This is really close....one thing I failed to mention though is the desired result is sometimes more than one word with spaces.

I.E. North Carolina 97, North Carolina St 84

Desired result = North Carolina St
Formula Result as written = North

Is there a way that to incorporate something that just trims the number at the end? The number is sometimes 3 characters instead of 2.
 
Upvote 0
andybord,

one thing I failed to mention though is the desired result is sometimes more than one word with spaces.

So that we can get it right the next time, how about a screenshot of the raw data, and, the results your are looking for.

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045
 
Upvote 0
Thanks! This is really close....one thing I failed to mention though is the desired result is sometimes more than one word with spaces.

I.E. North Carolina 97, North Carolina St 84

Desired result = North Carolina St
Formula Result as written = North

Is there a way that to incorporate something that just trims the number at the end? The number is sometimes 3 characters instead of 2.
Assuming the space after the comma will always be there, this formula should work for you...

=LEFT(MID(C2,FIND(",",C2)+2,999),MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(C2,FIND(",",C2)+2,999)&"0123456789"))-2)
 
Upvote 0
Have you tried Alladin's response in #2 and Rick's in #4?
 
Last edited:
Upvote 0
This will work, when the numbers at the end do not exceed three digits.

=LEFT(MID(A1,FIND(",",A1)+2,FIND(",",A1)+2),LEN(MID(A1,FIND(",",A1)+2,FIND(",",A1)+2))-3)
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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