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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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 Question
Removing Dupes
Replies
1
Views
368
DanteAmor
DanteAmor
D
  • Question Question
Static Timestamp
Replies
4
Views
948
Fluff
Fluff
N
  • Question Question
Read/Write between Excel and Text Files
Replies
4
Views
267
Vogateer
V
S
  • Question Question
TEXTJOIN/ CONCATENATE with numbering and formatting
2
Replies
11
Views
1K
Cubist
Cubist
D
  • Question Question
Trying to force people to not use a comma (even when pasting) or enter a number in a specific format
2 3
Replies
22
Views
826
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,909
Messages
6,175,315
Members
452,634
Latest member
cpostell

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