# Excel Right or Mid Function between a comma and a number

#### andybord

##### New Member
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Perhaps:

D2:

=TRIM(REPLACE(C2,1,SEARCH(",",C2),""))

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:
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:
andybord,

Welcome to the MrExcel forum.

Excel 2007
CD
1
2Duke 82, Kentucky 76Kentucky
3
Sheet1
Cell Formulas
RangeFormula
D2=TRIM(MID(SUBSTITUTE(C2," ",REPT(" ",100)),200,100))

Last edited:
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.

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:

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)

Have you tried Alladin's response in #2 and Rick's in #4?

Last edited:
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)

Replies
3
Views
174
Replies
2
Views
419
Replies
2
Views
426
Replies
20
Views
765
Replies
0
Views
383

1,196,319
Messages
6,014,625
Members
441,832
Latest member
tony tessman

### 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?

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