Copy text after last comma in cell

J

Juleew

New Member
Joined
Nov 24, 2009
Messages
45
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

:confused:
 
Sort by date Sort by votes
And may be the better is Txt = .Replace(Txt, LineSeparator) instead of Txt = .Replace(Txt, vbCrLf)
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Thank you so much for your help! :)

Right now I've got this:
I click a button and that saves my excel sheet as a csv-document. But now I can't run your "cleaning-code" (from ZVI) because the Excel-CSV- document is still open. Do you know a way how to crate a csv-document (maybe by asking for a different name than the original?) without changing the original?

Right now I've got this, but here I need to close the original (press: do not save changes) and open it again so I can press the button with the "cleaning-code"

Private Sub Save_Click()
'save document as csv
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Desktop\Folder\testdata.csv" _
, FileFormat:=xlCSV, CreateBackup:=False

'close original, don't save changes
ActiveWorkbook.Open savechanges:=False
Workbooks.Open Filename:="C:\Users\Desktop\Folder\Original.xlsm"
End Sub
 
Upvote 0
fro94 said:
... I can't run your "cleaning-code" (from ZVI) because the Excel-CSV- document is still open. Do you know a way how to create a csv-document (maybe by asking for a different name than the original?) without changing the original? ...
Click to expand...
Try this:
Rich (BB code): 
Private Sub Save_Click()
  
  Const CsvFile = "C:\Users\Desktop\Folder\testdata.csv"
   
  Dim Wb As Workbook
  
  ' Remember active workbook
  Set Wb = ActiveWorkbook
  
  ' Turn blinking off
  Application.ScreenUpdating = True
  
  ' Copy active sheet to the temporary new workbook
  ActiveSheet.Copy
  
  ' Save temporary workbook as CSV
  With ActiveWorkbook
    .SaveAs Filename:=CsvFile, _
            FileFormat:=xlCSV, CreateBackup:=False, _
            Local:=False
    ' Close it
    .Close False
  End With
  
  ' Restore selection
  Wb.Activate
  
  ' Restore screen updating
  Application.ScreenUpdating = True
  
  ' Clean up extra commas at the end of CSV lines
  FixCsv CsvFile
  
End Sub


Sub FixCsv(Optional CsvFile As String)
 
  Const LineSeparator = vbCrLf  ' <-- Change to suit
 
  Dim Txt As String, PathName As String, Fso As Object, File As Object
 
  ' Get PathName of CSV file
  If Len(CsvFile) = 0 Then
    ' 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
  Else
    ' Check the presence of CSV file
    PathName = CsvFile
    If Dir(PathName) = "" Then
      MsgBox "File not found:" & vbLf & PathName, vbCritical, "Exit"
      Exit Sub
    End If
  End If
 
  ' 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, LineSeparator)
  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
Regards
 
Last edited:
Upvote 0
That is great!

Is there the possibility to vary the name of the .csv-file by the content of sudden cells? Like this?

<code style="margin: 0px; padding: 0px; outline: 0px;">ActiveWorkbook.SaveAs Range("Table1!B2").Value & Range("Table1!C4").Value & ".csv"


And is it possible to save another workbook than the active one eg. "Table 2" instead of telling "ActiveWorkbook"?


Dim Wb As Workbook

' Remember active workbook
Set Wb = ActiveWorkbook
Click to expand...


Thanks a lot :)</code>
 
Upvote 0
Check the User Settings constant on the top of Save_Click() and try:
Rich (BB code): 
Private Sub Save_Click()
 
  ' --> User Settings, change to suit
  Const PathCell = "Table1!B2"  ' Path to CSV with "\" at the end, like "C:\Users\Desktop\Folder\"
  Const FNameCell = "Table1!C4" ' Name of CSV file, like "testdata"
  Const DataSheet = "Table2"    ' Name of the sheet with data for CSV file
  ' <-- End of User Setings
 
  Dim CsvFile As String, Wb As Workbook
 
  ' Build full path name to CSV file
  CsvFile = Range(PathCell).Value
  CsvFile = CsvFile & Range(FNameCell).Value & ".csv"
 
  ' Remember active workbook
  Set Wb = ActiveWorkbook
 
  ' Turn blinking off
  Application.ScreenUpdating = True
 
  ' Copy data sheet to the temporary new workbook
  Worksheets(DataSheet).Copy
 
  ' Save temporary workbook as CSV
  With ActiveWorkbook
    .SaveAs Filename:=CsvFile, _
            FileFormat:=xlCSV, CreateBackup:=False, _
            Local:=False
    ' Close it
    .Close False
  End With
 
  ' Restore selection
  Wb.Activate
 
  ' Restore screen updating
  Application.ScreenUpdating = True
 
  ' Clean up extra commas at the end of CSV lines
  FixCsv CsvFile
 
End Sub
 
 
Sub FixCsv(Optional CsvFile As String)
 
  Const LineSeparator = vbCrLf  ' <-- Change to suit
 
  Dim Txt As String, PathName As String, Fso As Object, File As Object
 
  ' Choose CSV file
  If Len(CsvFile) = 0 Then
    ' 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
  Else
    ' Check the presence of CSV file
    PathName = CsvFile
    If Dir(PathName) = "" Then
      MsgBox "File not found:" & vbLf & PathName, vbCritical, "Exit"
      Exit Sub
    End If
  End If
 
  ' 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, LineSeparator)
  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
    Err.Clear
  Else
    MsgBox "File is fixed now:" & vbLf & PathName, vbInformation, "Well done!"
  End If
 
End Sub
 
Upvote 0
ZVI said:
Check the User Settings constant on the top of Save_Click() and try:
Rich (BB code): 
Private Sub Save_Click()
 
  ' --> User Settings, change to suit
  Const PathCell = "Table1!B2"  ' Path to CSV with "\" at the end, like "C:\Users\Desktop\Folder\"
  Const FNameCell = "Table1!C4" ' Name of CSV file, like "testdata"
  Const DataSheet = "Table2"    ' Name of the sheet with data for CSV file
  ' <-- End of User Setings
Click to expand...


I get an error.... because of the "variable constants" I think. What other type of data can I use there to create the name of the csv-file?

Especially the error accours in line "CsvFile=Range(PathCell).Value
 
Upvote 0
fro94 said:
Especially the error accours in line "CsvFile=Range(PathCell).Value
Click to expand...
This means that you have no sheet named as "Table1".
Please do check the tab name of that sheet, its real name can be "Table 1" (with space char) then use Const PathCell = "'Table 1'!B2".
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

JTL9161
  • Question
Removing Dupes
Replies
1
Views
267
DanteAmor
DanteAmor
T
  • Solved
pivot table from columns with strings separated by commas
Replies
7
Views
294
Fluff
Fluff
H
  • Question
VBA split file based and send splitted file in separate emails
Replies
0
Views
114
harmless92
H
drom
  • Question
Validation list based on cell value
Replies
5
Views
162
mumps
mumps
N
  • Solved
Converting commas to full stops.
2
Replies
11
Views
657
arthurbr
arthurbr

Forum statistics

Threads
1,214,375
Messages
6,119,168
Members
448,870
Latest member
max_pedreira

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top