how to find a second comma in a text

Hi,

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

How can I find the position of the second comma?

thanks,
Boon


0
Boon
11/23/2009 5:27:21 PM
excel 39879 articles. 2 followers. Follow

9 Replies
1202 Views

Similar Articles

[PageSpeed] 19

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

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

-- 
Biff
Microsoft Excel MVP


"Boon" <boonyawat.la-ongthong@cnh.com> wrote in message 
news:OISZ4IGbKHA.2188@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> I have a text as -->     Madison, Dane, Wisconsin
>
> How can I find the position of the second comma?
>
> thanks,
> Boon
>
> 


0
T
11/23/2009 5:44:34 PM
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" <biffinpitt@comcast.net> wrote in message 
news:uYBsfSGbKHA.2184@TK2MSFTNGP04.phx.gbl...
> Assuming there is *always* a 2nd comma...
>
> =FIND(",",A2,FIND(",",A2)+1)
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "Boon" <boonyawat.la-ongthong@cnh.com> wrote in message 
> news:OISZ4IGbKHA.2188@TK2MSFTNGP04.phx.gbl...
>> Hi,
>>
>> I have a text as -->     Madison, Dane, Wisconsin
>>
>> How can I find the position of the second comma?
>>
>> thanks,
>> Boon
>>
>>
>
> 


0
Boon
11/23/2009 7:51:51 PM
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...

Boon wrote:
> 
> 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" <biffinpitt@comcast.net> wrote in message
> news:uYBsfSGbKHA.2184@TK2MSFTNGP04.phx.gbl...
> > Assuming there is *always* a 2nd comma...
> >
> > =FIND(",",A2,FIND(",",A2)+1)
> >
> > --
> > Biff
> > Microsoft Excel MVP
> >
> >
> > "Boon" <boonyawat.la-ongthong@cnh.com> wrote in message
> > news:OISZ4IGbKHA.2188@TK2MSFTNGP04.phx.gbl...
> >> Hi,
> >>
> >> I have a text as -->     Madison, Dane, Wisconsin
> >>
> >> How can I find the position of the second comma?
> >>
> >> thanks,
> >> Boon
> >>
> >>
> >
> >

-- 

Dave Peterson
0
Dave
11/23/2009 8:28:10 PM
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" <petersod@verizonXSPAM.net> wrote in message 
news:4B0AF05A.1F0F5790@verizonXSPAM.net...
> 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...
>
> Boon wrote:
>>
>> 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" <biffinpitt@comcast.net> wrote in message
>> news:uYBsfSGbKHA.2184@TK2MSFTNGP04.phx.gbl...
>> > Assuming there is *always* a 2nd comma...
>> >
>> > =FIND(",",A2,FIND(",",A2)+1)
>> >
>> > --
>> > Biff
>> > Microsoft Excel MVP
>> >
>> >
>> > "Boon" <boonyawat.la-ongthong@cnh.com> wrote in message
>> > news:OISZ4IGbKHA.2188@TK2MSFTNGP04.phx.gbl...
>> >> Hi,
>> >>
>> >> I have a text as -->     Madison, Dane, Wisconsin
>> >>
>> >> How can I find the position of the second comma?
>> >>
>> >> thanks,
>> >> Boon
>> >>
>> >>
>> >
>> >
>
> -- 
>
> Dave Peterson 


0
Boon
11/23/2009 8:57:21 PM
On Mon, 23 Nov 2009 14:57:21 -0600, "Boon" <boonyawat.la-ongthong@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
0
Ron
11/23/2009 9:21:50 PM
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
dguillett1@austin.rr.com
"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:vvulg5t0jl8bjtgc70c4dsjj1obq61hkto@4ax.com...
> On Mon, 23 Nov 2009 14:57:21 -0600, "Boon" <boonyawat.la-ongthong@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 

0
Don
11/24/2009 5:48:05 PM
Ron,

Thanks for your help. Your trick is awesome!
"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:vvulg5t0jl8bjtgc70c4dsjj1obq61hkto@4ax.com...
> On Mon, 23 Nov 2009 14:57:21 -0600, "Boon" <boonyawat.la-ongthong@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 


0
Boon
11/24/2009 8:46:05 PM
On Tue, 24 Nov 2009 14:46:05 -0600, "Boon" <boonyawat.la-ongthong@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
0
Ron
11/25/2009 1:27:06 AM
Very neat solution, I like this a lot.

Ron Rosenfeld wrote:
> On Mon, 23 Nov 2009 14:57:21 -0600, "Boon" <boonyawat.la-ongthong@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
0
AdamV
11/25/2009 9:17:12 AM
Reply:

Similar Artilces:

Text Box Linked To Cell
My goal is to have a text box that displays the contents of a particular cell. How do I link the text box to the cell? Thanks, Denise Hi denise, Make a Text Box from the drawing toolbar. Select the box and in the Formula Bar, type = and then select the cell, press enter. ( IE: =$C$5 ) The cell can also be on a different sheet, just do the same. ( IE: =Sheet2!C5 ) Dave denise Wrote: > My goal is to have a text box that displays the contents of a > particular > cell. How do I link the text box to the cell? > > Thanks, > Denise -- Piranha --------------------------...

Modify Exchange undeliverable email text
Is there a way in Exchange 2003 to modify the text of the message that is sent for an undeliverable email address? Thank You Jason Shaw Not natively. James Chong MCSE + Messaging, MCTS msexchangetips.blogspot.com Jasons9745 wrote: > Is there a way in Exchange 2003 to modify the text of the message that > is sent for an undeliverable email address? > Thank You > Jason Shaw Not easily done. Jasons9745 wrote: > Is there a way in Exchange 2003 to modify the text of the message that > is sent for an undeliverable email address? > Thank You > Jason Shaw > > -...

Disappearing Text
Hi. All my new and existing mail in Outlook Express has the subject line and the from populated, but the body of the message won't come up. We get the hourglass and it never goes away. Any ideas? Please email answers to kevinbrand@earthlink.net. Thanks Kevin Hi, Start --> Run --> Regsvr32 inetcomm.dll Hope this helps !!! With Regards, Sudharson.AN "Kevin" <anonymous@discussions.microsoft.com> wrote in message news:1284e01c41826$c0787480$a001280a@phx.gbl... > Hi. All my new and existing mail in Outlook Express has > the subject line and the from populate...

Inserting a string of text in front of existing text in excel
How do I insert a word or words in front of existing text cells on a multiple cell basis ? Hi try something like the following macro (works on your selection): sub foo() dim rng as range dim cell as range dim str_part set rng=selection str_part="precede_" for each cell in rng if cell.value<>"" then cell.value=str_part & cell.value end if next end sub >-----Original Message----- >How do I insert a word or words in front of existing text >cells on a multiple cell basis ? >. > If a formula will do, try something like this (with the existing text...

OWA
OWA text field is always 'grayed out' - on new messages and replies. Small "x" button in upper left of text field seems to have no functionality. Subject field is OK. Bummer - do you have a question? If yes, then try asking it in an Exchange news group as Outlook Web Access is a function of Exchange, not Outlook. Microsoft.public.exchange.admin is a good place to start. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted w...

Problem creating full text index
I seem to be unable to create a full text index of either my public or private store. When I try to creat the index I get the error; The directory name is invalid. Facility: MSSearch.Admin.1 ID no: 8007010b Exchange system Manager This seems to be happening for both the public and private stores. The directory does exist. I have looked over the permissions and tried to compare to known working servers and I haven't moticed any obvious differences. Any ideas? Did you follow this article: http://support.microsoft.com/kb/822932/en-us Nue "Jonathan Huber" <JonathanHu...

converting from text to numerical number
I have a spread sheet of entire year schedule. The format as following: 1 2 3 .... 31 row 1 row 2 row 3 Jan row 4 row 5 Feb row x Dec I would like to replace column 1 up to Jan to 1. Replace the cell below Jan to Feb to 2. etc. Would anyone help. Thanks in advance. You could select cells A1:A3, type a 1, then press Ctrl+Enter Select A4:A5, type a 2, and press Ctrl+Enter And so on, till all twelve months are done. james wrote: > I have a spread sheet of entire year schedule. The format as following: > > 1 2 3 .... 31 &...

Input text message (dialog box) for data validation
Hello all: This may sound idiotic to some, but I've already been wasting too much time with this option, and am looking for some guru insight. I am trying to input text into the Data Validation Text Box that looks something like this: ------------------------------------------------------------------------------------------------------------------------------------- Q3) Was this a prospective or retrospective analysis or study documenting the effects of XYZ that compared and reported the data in the Marketing Strategy Program? a) Yes b) No c) This appears to be a concise comparison, but ot...

Access 2007 Special Effect Text Box Won't show Sunken with Windows XP
Hi All I am just getting to grips with Access 2007 and one of the quirks I have found is that when I design using Windows in XP Windows-Classic colors and set the text boxes on forms' special effect property to "sunken" They appear as sunken in the correct manner. However, when I change to the Windows XP color scheme the forms text boxes appear flat even though the special effect property has not changed. Any ideas or is it a new quirk we have to live with. I haven't tried it with Vista yet. That should be interesting Thanks Bob I am quite sure this is the same for acce...

randomly scatter text
I have pub. 2003, I need to know how to take a phrase and randomly scatter it allover the page, with out placing it manually. I was able to do it on the last version of publisher but I can't seem to find the random button on this one. PLEASE HELP!!! Tabs? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "jenice" <jenice@discussions.microsoft.com> wrote in message news:C2C51271-D425-4E3E-8A71-55365A05D774@microsoft.com... >I have pub. 2003, I need to know how to take a phrase and randomly scatter it > all...

Macro: Find matching DATA, copy correspondently values into another list
Okey guys - please advise me on this. (There was unfortunately no quick response to my latest problem - and I begun to swim... However - that brought me out on deep VBA-water...again - and now need a life buoy (metaphorical spoken)). The core of my macro problem is to look for all the values in column A (Range A5:A??) in Sheet "BOARD" (not bored!!!) that match values in another list placed in column A in sheet "LIST". When finding the match I will copy correspondent celle.value in column D / sheet "BOARD" into column B in sheet "LIST" - this w...

How to Find Record if Exists, Add if not?
How to Find Record if Exists, Add if not? I have a form where user will enter the vendor ID, which is a unique ID. They enter it off a long list of information listing many vendors and it's hard to know if the vendorID already exists in the database, until they tab through all the fields and get the duplicate record error. Then they press Esc and do a search on that vendorID to pull up the existing record to update. VendorID format is similar to this A123456789ST (Usees a prefix, Vendor FEIN, State abbrev.) I want to automate this. when they enter the VendorID, I want it to s...

Finding data
Hi all, first post here at these forums :) I have a question/ problem that hopefully someone can answer for me. If anyone has the time, could they have a look at the attachment, i was easier that trying to explain it. But in a nutshell it involve finding data within sheets and totaling them on another sheet. The problem is that in my real world example, I will have 40 sheets and 100 rows of data, so performing it manually is out of th question. cheers, and thanks for your time. Charli +------------------------------------------------------------------- |Filename: help1.zip ...

ADO Find method
I would like to know if there is a way to use the ADO Find method to find one of several values for a single column. See the following example: rstOpenJobProcess.Find "[proc#] = '940' or '941'" I would like to search for proc# '940' or '941'. I want to avoid using a like statement in the criteria (such as [proc#] Like '94*') because I may use '942' for something else in the future that I would not want to search for. Is there a way to use the Find method in this way? Alternatively, is there a clever workaround? Th...

Sorting imported text
I have a spreadsheet into which I've imported a great deal of data in two columns only. I set out rows one, three and five of hundreds of such rows. The cells in column A consist of place names. (The "C" following the place name stands for "City", the "A" for "Area".) The cells in column C represent the population of the city or area, as the case may be. I've formatted the cells in column C as numbers. Albury (C) 44887 Armidale Dumaresq (A) 24596 Ashfield (A) 40258 I want to sort the placenames in order of their po...

Text Function with Different Formatting for Number
I'm trying to use a text function where I reference a number from another cell but I need the text part to be bold and the number not to be bold. This is the formula I'm using right now: ="Sales: "&TEXT(A16,"$#,##0_);($#,##0)") Unfortunately, excel won't let me just make the text bold without making the number bold. Anyone have any suggestions? Thanks M, I think a macro could do this, since it's really a string you're going to wind up with. But if you can put the Sales: part in a separate cell, life will be much easier anyway. then you ...

Adjusting column widths to fit text plus extra space
I can get Excel to adjust one or more columns to fit the data by selecting the columns and double-clicking on one of the column header borders. The result is the smallest possible width for the existing data. This is a little too tight for optimum viewing. Is there a way to tell excel to choose a width that is slightly wider? In Word, there is a *Cell Margin* setting for tables. Is there anything similar in Excel? Column Auto-width has no cell margins parameters that can be set. Gord Dibben MS Excel MVP On Tue, 14 Jun 2011 06:50:36 -0700, Jennifer Murphy <JenMurphy@geospace.org>...

Advanced find and seeing full path to mail
When doing an advanced find Outlook only show the sub- folder it is in. How do you get it to list the full path. Listing a sub-folder can be very hard if there are folders with the same name. This is with Outlook 2000. ...

Combo and text box dependency
On my form I have a combo box (cboRepairType) which retrieves its list from field(1) of tblGrips. Once the selection has been made (AfterUpdate I presume) I would like the corresponding value from field(3) of tblGrips to display in a text box (txtSellPrice), (or would another type of display be more appropriate?) Can this be done? Please be gentle I am a novice at this :-) Sandy Hi Sandy, Yes, it can be done. Use the Afterupdate event procedure, as you have indicated. In VBA code, the columns are "zero-based", so the first column of the rowsource would be column(0), the s...

Asked before cannot find question or answer anymore "Caracter mess
When I am using Word I have no problem typing this caracter ? (question mark). however if I type this in OOUTLOOK, than it appears like this É or like this _ It has just done it lately, so I must have changed a setting unknowingly. Can someone please help to get the question mark ? back again... thank you. By the way this time I have checked the "Notify me of repies" Verify your keyboard language and layout settings in Regional and Language option in Control Panel. For more details see; http://www.msoutlook.info/question/16 -- Robert Sparnaaij [MVP-Outlook] Coauthor, C...

Align text in listbox ???
Why does the text in some of my listboxes left align and some center align. There is no text align property to control this. Using Access 2003. Thanks. What is the row source of the listbox where the text is not aligned the way you want? Jeanette Cunningham "mscertified" <rupert@tigerlily.com> wrote in message news:6D035E8B-2CB6-4BDB-B3D3-0A7EFD20C0FE@microsoft.com... > Why does the text in some of my listboxes left align and some center > align. > There is no text align property to control this. > Using Access 2003. > > Thanks. ...

outlook express messages not showing text
Help! my outlook express is not showing the text for incoming messages. I am not sure what happened or was pushed to make this happen. I can go in to properties and view the message that way or export it into outlook to view. I have tried view and it says show all messages. Can anyone help me? This newsgroup is for support of Outlook 97, 98, 2000 & 2002 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress...

Find a "9" within a range using a function (T/F)
I need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere within a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I could use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or false; I don't need to know where the nine is or any other info. I just need to know if it is there. I'm stumped. Any ideas? Thanks in advance! -- RMC,CPA Hi! Try this: =SUMPRODUCT(--(ISNUMBE...

Find User Object based on Logon Name
I need to find the location of the user object in AD based on the Logon Name IE: I have: JDoe123 (the logon name) I need: LDAP://cn=JohnDoe,ou=users,ou=aaa,ou=bbb,ou=ccc,DC=xxx thanks! By the way - a clarification: i dont know what ou the object is in, so i can not assume it lives in ou=users,ou=aaa,ou=bbb,ou=ccc,DC=xxx "pk" <pk@discussions.microsoft.com> wrote in message news:1F6CD335-545E-48DE-80DB-0E2E351AA177@microsoft.com... >I need to find the location of the user object in AD based on the Logon >Name > > IE: > I have: > ...

Using comma to scale large numbers?
I just discovered that a comma can be added to a custom number format to scale it by three orders of magnitude. Here are a few examples: # #,##0 #,##0, #,##0,, #,##0,,, 0 0 0 0 0 1 1 0 0 0 499 499 0 0 0 500 500 1 0 0 1,499 1,499 1 0 0 1,500 1,500 ...