how to find a second comma in a text

B

Boon

Hi,

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

How can I find the position of the second comma?

thanks,
Boon
 
B

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
 
D

Dave Peterson

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...
 
B

Boon

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
 
R

Ron Rosenfeld

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
 
D

Don Guillett

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
 
R

Ron Rosenfeld

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
 
A

AdamV

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access import CSV file with comma issue 4
Save as comma csv problems 3
select text to the left of a comma - Access 1
viewing CSV file in Excel-2000 3
Saving As .CSV 9
Cannot view email message from my daughter at University of Wiscon 1
Parsing cells to reformat a multi-entry field database 2
Exporting data in comma delimited format in text file 1

Top