Copy text after last comma in cell

[Juleew]

I am using excel 2007, I have a column of cells that contain text that is separated by commas. Each cell may have 3 commas or 5 or 2 commas. What I need to do is copy the last text after the last comma in the cell.

example
xxx,yyy,zzz copy to another cell the text zzz
xxx,yyy copy to another cell the text yyy
xxx,yyy,zzz,aaa copy to another cell the text aaa

 

[Rick Rothstein]

I have a similar issue. I am trying to copy text from one cell to another. The first cell contains a very long text string with the information separated by commas and enclosed by quotation marks. How do I pick and choose the text within the quotations I would like to extract? I would like to achieve this using a formula.

Example of A1:
"Internal/External Agent Calls Report","Report data from: Saturday, March 22, 2014 to Saturday, March 29, 2014","Group By","Outbound Complete","Outbound InComplete","Internal Complete","Internal Incomplete"," # - Name","Number of Calls","Average Duration","Number of Calls","Average Duration","Number of Calls","Average Duration","Number of Calls","Average Duration",,,,,,,,,,,"Filters Applied:","Relative Date:",,"Agent Group Number:","Enterprise Group ID(s):",,"71","Agent Number:","30000-39999","Display Level:","Sort Order:","Enterprise Group, Agent Group, Agent, Call Detail","by ID","Start Time:","2014-03-22","Start Time:","End Date:","00:00:00","2014-03-29","End Time:","23:59:59","Time Zone:","LOCAL","38,39,40,41,45,46","Dialed Digits: ","200",," Tuesday, April 15, 2014 15:41:42","Page -1 of 1"

Example of output I would like:
A2
Report data from: Saturday, March 22, 2014 to Saturday, March 29, 2014
A3
Sort Order:
A4
Dialed Digits:
A5
200

Will your text always have the same number of commas every time? If so, will you always know which field number you want to pull from it? If not, then how will you know which text you want to retrieve?

 

[jc352]

Yes, it is a standard report being used that has the same format every time it is run.

 

[Rick Rothstein]

Yes, it is a standard report being used that has the same format every time it is run.

Okay, I think the size of the text in your cells are too large and that a formula solution to pick and choose fields will be inefficient, so I am going to suggest that you use a UDF (user defined function) instead...

Function QuoteCommaSplit(ByVal TextToSplit As String, FieldToRetrieve As Long) As String
  QuoteCommaSplit = Split("""," & TextToSplit & ",""", """,""")(FieldToRetrieve)
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. The first argument is the cell where the text is and the second argument is the field number you want to retrieve. For example,

=QuoteCommaSplit(A1,2)

which will return "Report data from: Saturday, March 22, 2014 to Saturday, March 29, 2014" from the text in A1, or...

=QuoteCommaSplit(A1,22)

which will return "Sort Order:" from the text in A1, or...

=QuoteCommaSplit(A1,36)

which will return "Dialed Digits:" from the text in A1.

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[jc352]

Thank you very much for your help, that worked perfectly.

 

[jc352]

Can this be tweaked to look for commas and remove the beginning and ending quotes if they exist?

 

[Rick Rothstein]

Can this be tweaked to look for commas and remove the beginning and ending quotes if they exist?

What beginning and ending quotes? Do you mean for the entire text string (I wouldn't think there would be any) or the internal quoted text (I wouldn't think you would want to)?

 

[jc352]

I was able to use MID in addition to that function to clean it up. I really appreciate the help on this.

 

[sahaider]

hello ,
I have some values separated by commas as below:
,,,,,,345,567,678
,,,456,234,789,890,456,234
,,,,,,,,567,234,567,456

What I want is to get rid all the leading commas but retain the commas between values, so for example for the first example i have 345,567,678 as output.

 

[billszysz]

=RIGHT(A1,LEN(A1)-MATCH(TRUE,ISNUMBER(-MID(A1,ROW($1:$99),1)),0)+1)

or

=RIGHT(A1,LEN(A1)-MATCH(TRUE,ISNUMBER(-MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),0)+1)

You can increase ROW arguments if you need.

Regards

 

[Rick Rothstein]

hello ,
I have some values separated by commas as below:
,,,,,,345,567,678
,,,456,234,789,890,456,234
,,,,,,,,567,234,567,456

What I want is to get rid all the leading commas but retain the commas between values, so for example for the first example i have 345,567,678 as output.

Assuming you want to get rid of those commas directly within the data cells themselves (as opposed to placing the modified values in another cell), give this macro a try...

Sub RemoveLeadingCommas()
  Dim X As Long, Data As Variant
  Const DataCol As String = "B"
  Const DataStartRow As Long = 3
  Data = Cells(1, DataCol).Resize(Cells(Rows.Count, DataCol).End(xlUp).Row)
  For X = 1 To UBound(Data)
    Data(X, 1) = Format$(Replace(Data(X, 1), ",", ""), "0,000")
  Next
  Cells(1, DataCol).Resize(UBound(Data)).NumberFormat = "@"
  Cells(1, DataCol).Resize(UBound(Data)) = Data
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (RemoveLeadingCommas) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.