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

 

[shawnw]

Good advice. Thanks.

So now my formula looks like this:

=TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",300)),COLUMNS($B:B)*300,300))

And this create a table that looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]COL A[/TD]
[TD]COL B[/TD]
[TD]COL C[/TD]
[TD]COL D[/TD]
[TD]COL E[/TD]
[/TR]
[TR]
[TD]Apple,Banana,Pear[/TD]
[TD]Apple[/TD]
[TD]Banana[/TD]
[TD]Pear[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana,Watermelon,Grape[/TD]
[TD]Banana[/TD]
[TD]Watermelon[/TD]
[TD]Grape[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grape,Lemon,Watermelon,Kiwi[/TD]
[TD]Grape[/TD]
[TD]Lemon[/TD]
[TD]Watermelon[/TD]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD]Lemon,Lime,Kiwi,Pear[/TD]
[TD]Lemon[/TD]
[TD]Lime[/TD]
[TD]Kiwi[/TD]
[TD]Pear[/TD]
[/TR]
</tbody>[/TABLE]

Note that I created a named range called "RangeFruits" covering $B:$F.

This is perfect (thanks again). Now I need the pie chart. Here's my thinking:

1. Col F: Left blank so as to avoid confusion between sets of data
2. Col F: A formula that lists out all the unique values in alphabetical order. The number of rows needed depends on the data.
3. Col G: The count of instances of each unique value within the "Fruits" named range.

Example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]COL A[/TD]
[TD]COL B[/TD]
[TD]COL C[/TD]
[TD]COL D[/TD]
[TD]COL E[/TD]
[TD]COL F[/TD]
[TD]COL G[/TD]
[TD]COL H[/TD]
[/TR]
[TR]
[TD]Apple,Banana,Pear[/TD]
[TD]Apple[/TD]
[TD]Banana[/TD]
[TD]Pear[/TD]
[TD][/TD]
[TD]----[/TD]
[TD]Apple[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Banana,Watermelon,Grape[/TD]
[TD]Banana[/TD]
[TD]Watermelon[/TD]
[TD]Grape[/TD]
[TD][/TD]
[TD]----[/TD]
[TD]Banana[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Grape,Lemon,Watermelon,Kiwi[/TD]
[TD]Grape[/TD]
[TD]Lemon[/TD]
[TD]Watermelon[/TD]
[TD]Kiwi[/TD]
[TD]----[/TD]
[TD]Grape[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Lemon,Lime,Kiwi,Pear[/TD]
[TD]Lemon[/TD]
[TD]Lime[/TD]
[TD]Kiwi[/TD]
[TD]Pear[/TD]
[TD]----[/TD]
[TD]Kiwi[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]----[/TD]
[TD]Lemon[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]----[/TD]
[TD]Lime[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]----[/TD]
[TD]Pear[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]----[/TD]
[TD]Watermelon[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I looked at some online forums and tried monkeying around with some formulas, but... no joy.

This is what I have so far, but it's way off the mark, I think:

=INDEX(RangeFruits,MATCH(0,COUNTIF(RangeFruits,"<"&RangeFruits)-SUM(COUNTIF(RangeFruits,"="&G$1:G1)),0))

What am I doing wrong?

Thanks,

Shawn

 

[shawnw]

One correction. The named range covers $B:$E, not $B:$F.

 

[shawnw]

So now I'm trying something like this

=IFERROR(INDEX(RangeFruit, MATCH(0,IF(ISBLANK(RangeFruit),1,COUNTIF($H$2:H2, RangeFruit)), 0)),"")

Still no joy.

 

[fro94]

Hello Community,
I’m a beginner with excel and VBA and so I need your help :/
With excel I generate a csv-file which I need for an CAE-programme (ANSA and NASTRAN). Therefore I need my “final-file” to be opened in windows Wordpad.
Now there are sudden problems.
For example: my csv-file looks like this (there are several rows (different lengths), some end with 4 others with 1 or more commas)

GRID,20000000,20000002,0.0,0.0,0.0,20000001,,,,
CBEAM,20000000,2000010,20000002,20000004,1.0,0.0,0.0,,,
PBEAML,2000010,2000010,MSCBML0,TUBE,,,,,+,
+,15.10,9.10,,,,,,,,

Now I need to know how to get rid of the last commas in each row but without losing the other commas that separate sudden values.
I’d be very thankful for your help. It’d be nice if you know a way how to control that Wordpad with a button-click in my excel datasheet.
I know that there is a post with a familiar topic but I can’t transfer it to my needs because I need to edit the csv-file in Wordpad … :/
Thank you so much for your help.

 

[István Hirsch]

Try this:

Excel Workbook
	A	B
1	GRID,20000000,20000002,0.0,0.0,0.0,20000001,,,,	GRID,20000000,20000002,0.0,0.0,0.0,20000001
2	CBEAM,20000000,2000010,20000002,20000004,1.0,0.0,0.0,,,	CBEAM,20000000,2000010,20000002,20000004,1.0,0.0,0.0
3	PBEAML,2000010,2000010,MSCBML0,TUBE,,,,,+,	PBEAML,2000010,2000010,MSCBML0,TUBE,,,,,+
4	+,15.10,9.10,,,,,,,,	+,15.10,9.10
Sheet1

 

[fro94]

Thank you very much for your answer. Have you got an VBA-Code for this function?

 

[Rick Rothstein]

Thank you very much for your answer. Have you got an VBA-Code for this function?

There are a few ways you can attack this problem in VBA, but the simplest would be as follows. Let's assume Txt is a variable containing the text and you want to find what follows the last comma...

TextAfterLastComma = Trim(Mid(Txt, InStrRev(Txt, ",") + 1))

 

[ZVI]

Here is VBA function to trim all commas at the end of string

Function RTrimCommas(txt As String) As String
  Static rgx As Object
  If rgx Is Nothing Then
    Set rgx = CreateObject("vbscript.regexp")
    rgx.Global = True
    rgx.Pattern = ",+$"
  End If
  RTrimCommas = rgx.Replace(txt, vbNullString)
End Function

Usage in a cell formula: =RTrimCommas(A1)
Result is the same as in the post #35

 

[Rick Rothstein]

Please ignore what I posted in Message #37 ... I misread what you wanted. As to what you wanted, assuming the data you posted in Message #34 is truly representative of your actual data, and that data contains no spaces, there is a very simple function that you can use to remove those trailing commas...

Function RTrimCommas(Txt As String) As String
  RTrimCommas = Replace(RTrim(Replace(Txt, ",", " ")), " ", ",")
End Function

 

[ZVI]

Below is the code to clean up all commas at the end of lines in the chosen CSV file

Sub FixCsv()
 
  Const LineSeparator = vbCrLf  ' <-- Change to suit
 
  Dim Txt As String, PathName As String, Fso As Object, File As Object
 
  ' Choose CSV file
  With Application.FileDialog(msoFileDialogOpen)
    .InitialFileName = ThisWorkbook.Path
    .Filters.Clear
    .Filters.Add "CSV files", "*.csv"
    .AllowMultiSelect = False
    If .Show = False Then Exit Sub
    PathName = .SelectedItems(1)
  End With
 
  ' Trap errors
  On Error GoTo exit_
 
  ' Read CSV file
  Set Fso = CreateObject("Scripting.FileSystemObject")
  Set File = Fso.OpenTextFile(PathName, 1)
  Txt = File.ReadAll
  File.Close
 
  ' Trim all commas at the end of lines
  If Right(Txt, Len(LineSeparator)) <> LineSeparator Then Txt = Txt & LineSeparator
  With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = ",+" & LineSeparator
    Txt = .Replace(Txt, vbCrLf)
  End With
   
  ' Write CSV
  Set File = Fso.CreateTextFile(PathName, True)
  File.Write Txt
  File.Close
 
exit_:
 
  ' Report
  If Err Then
    MsgBox Err.Description, vbCritical, "Error #" & Err.Number
  Else
    MsgBox "File is fixed now:" & vbLf & PathName, vbInformation, "Well done!"
  End If
 
End Sub