how to find a second comma in a text

  • Thread starter Thread starter Boon
  • Start date Start date
B

Boon

Hi,

I have a text as --> Madison, Dane, Wisconsin

How can I find the position of the second comma?

thanks,
Boon
 
thanks. this works well for me. but if I have more than 2 commas. and I
don't know whether it is 2 or 3 or .....

what would be the formula. I am thinking of using FIND function but wnat to
have excel finds it from the right. is it possible?

thanks,
Boon
 
It may be better to share what you're really trying to do.

For instance, if you wanted to parse your entry into separate columns (separated
by commas), there may be easier ways.

Or if you wanted the last string after the last comma

Or whatever...
 
As I mentioned in my first posting. Let me clarify more in detail.

I have a text in column A. And I want to extract the state name into column
B

samples of text in coulmn A.

Madison, Dane, Wisconsin
Chicago, Illinois
Sheboygan village, Madison, Dane, Wisconsin
Oregon
Seattle, Washington
 
As I mentioned in my first posting. Let me clarify more in detail.

I have a text in column A. And I want to extract the state name into column
B

samples of text in coulmn A.

Madison, Dane, Wisconsin
Chicago, Illinois
Sheboygan village, Madison, Dane, Wisconsin
Oregon
Seattle, Washington
Click to expand...

That is a very different question from:

"How can I find the position of the second comma?"

And, given your variability, the answer to your first question would really not
do you much good.

A better question might be to return the last "comma-separated" substring from
a string; or the entire string if there is no comma.

The answer to that could be something like:

=TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99))
--ron
 
Or a macro
Sub getlastcommatoright()
mc = 1 ' col A
For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row
x = InStrRev(Cells(i, mc), ",")
Cells(i, mc).Offset(, 1) = _
Right(Cells(i, mc), Len(Cells(i, mc)) - x)
Next i
End Sub
 
Ron,

Thanks for your help. Your trick is awesome!
Click to expand...

You're welcome. Glad to help. Thanks for the feedback.

Also note that it was Dave's question that elicited a better explanation of
what you needed to accomplish -- without that, we would have all been guessing.

--ron
 
Very neat solution, I like this a lot.

Ron said:
That is a very different question from:

"How can I find the position of the second comma?"

And, given your variability, the answer to your first question would really not
do you much good.

A better question might be to return the last "comma-separated" substring from
a string; or the entire string if there is no comma.

The answer to that could be something like:

=TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99))
--ron
Click to expand...
 
Back
Top