how to find a second comma in a text

Boon

unread,

Nov 23, 2009, 12:27:21 PM11/23/09

to

Hi,

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

How can I find the position of the second comma?

thanks,
Boon

T. Valko

unread,

Nov 23, 2009, 12:44:34 PM11/23/09

to

Assuming there is *always* a 2nd comma...

=FIND(",",A2,FIND(",",A2)+1)

--
Biff
Microsoft Excel MVP

"Boon" <boonyawat....@cnh.com> wrote in message
news:OISZ4IGb...@TK2MSFTNGP04.phx.gbl...

Boon

unread,

Nov 23, 2009, 2:51:51 PM11/23/09

to

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

"T. Valko" <biffi...@comcast.net> wrote in message
news:uYBsfSGb...@TK2MSFTNGP04.phx.gbl...

Dave Peterson

unread,

Nov 23, 2009, 3:28:10 PM11/23/09

to

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

--

Dave Peterson

Boon

unread,

Nov 23, 2009, 3:57:21 PM11/23/09

to

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

"Dave Peterson" <pete...@verizonXSPAM.net> wrote in message
news:4B0AF05A...@verizonXSPAM.net...

Ron Rosenfeld

unread,

Nov 23, 2009, 4:21:50 PM11/23/09

to

On Mon, 23 Nov 2009 14:57:21 -0600, "Boon" <boonyawat....@cnh.com>
wrote:

>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

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

Don Guillett

unread,

Nov 24, 2009, 12:48:05 PM11/24/09

to

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com
"Ron Rosenfeld" <ronros...@nospam.org> wrote in message
news:vvulg5t0jl8bjtgc7...@4ax.com...

Boon

unread,

Nov 24, 2009, 3:46:05 PM11/24/09

to

Ron,

Thanks for your help. Your trick is awesome!

"Ron Rosenfeld" <ronros...@nospam.org> wrote in message
news:vvulg5t0jl8bjtgc7...@4ax.com...

Ron Rosenfeld

unread,

Nov 24, 2009, 8:27:06 PM11/24/09

to

On Tue, 24 Nov 2009 14:46:05 -0600, "Boon" <boonyawat....@cnh.com>
wrote:

>Ron,
>
>Thanks for your help. Your trick is awesome!

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

AdamV

unread,

Nov 25, 2009, 4:17:12 AM11/25/09

to

Very neat solution, I like this a lot.

ziya.urre...@gmail.com

unread,

Mar 4, 2017, 3:27:57 AM3/4/17

to

Dear i have a value for example "zia,khan,zia,zia,jan,khan,ali,Rehma" i need 2nd last value Ali After comma

isabelle

unread,

Mar 6, 2017, 3:14:23 PM3/6/17

to

Hi,

=MID(A2,SEARCH("§",SUBSTITUTE(A2,",","§",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))-1))+1,SEARCH("§",SUBSTITUTE(A2,",","§",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))-SEARCH("§",SUBSTITUTE(A2,",","§",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))-1))-1)

A2 = "zia,khan,zia,zia,jan,khan,ali,Rehma
B2 = Total number of commas 7 --> =LEN(A2)-LEN(SUBSTITUTE(A2,",",""))
C2 = Position of the last comma 31 --> =SEARCH("§",SUBSTITUTE(A2,",","§",B2))+1
D2 = Position of the last-to-last comma 27 -->
=SEARCH("§",SUBSTITUTE(A2,",","§",B2-1))+1
E2 = result ali --> =MID(A2,D2,C2-D2-1)

isabelle

isabelle

unread,

Mar 6, 2017, 11:33:15 PM3/6/17

to

...also you can distribute the contents of a cell into adjacent columns
https://support.office.com/en-us/article/Distribute-the-contents-of-a-cell-into-adjacent-columns-ce6b3ffd-2ed9-4ee7-a7cc-86ef70121290?ui=en-US&rs=en-US&ad=US
isabelle

christophe...@pottsvilleschools.org

unread,

Mar 8, 2017, 5:07:59 PM3/8/17

to

isabelle

unread,

Mar 8, 2017, 5:55:54 PM3/8/17

to

Hi,

Assumes text is in cell A2
=SEARCH("§",SUBSTITUTE(A2,",","§",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))

isabelle