• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

delete all formatting after last cell character

I have a spreadsheet with 1000 email addressed imported into it. However, they do not have the underlining  nor do they operate as an email. I discovered that there is extraneous formattingin the cells that prevents me from using the emails.
I would like to have some vba code that steps through each cell in the usedrange and delete all formatting  after the comma  ending the emal address

Is it possible using delete or truncate to get rid of the extraneous formatting on the right side of the cell? If so , please let me know how
0
llawrenceg
Asked:
llawrenceg
  • 4
  • 3
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try
Sub Doit()

For Each cell In ActiveSheet.UsedRange
    If InStr(1, cell.Text, ",") Then
        cell.Value = Left(cell.Text, InStr(1, cell.Text, ","))
    End If
Next ' cell
End Sub

Open in new window

Regards
0
 
llawrencegAuthor Commented:
Did not work. I've attached a mini dummy file to help. The original file has several rows of addresses but they are not "live" addresses. I have to delete the character space right after the comma and hit enter for the address to convert to a mailto: address.
I'm sending an email to hundreds and the email system  requires working email addresses each separated by a comma.
So if you can show me how to code for the delete after the comma and clicking enter, I would appreciate it
email-converter.xls
0
 
Rgonzo1971Commented:
Hi,

If I understand you well

 Hake, John@yahoo.com, sould become  Hake,John@yahoo.com,
Sub DoitOncemore()

For Each cell In ActiveSheet.UsedRange
        cell.Value = Replace(cell.Text, " ", "")
Next ' cell
End Sub

Open in new window


Regards
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
llawrencegAuthor Commented:
Not quite .
So if you can show me how to code for  deleting everthing after the last comma and clicking enter, I would appreciate it
I made an error on samle file
the addresses look like this
Preston.Rosemarie@yahoo.com, Hake.John@yahoo.com, Senna.Sharon@yahoo.com,
With a period in the name and a comma between addresses.

So I need to delete everything after the last comma and then hit enter for each cell
0
 
Rgonzo1971Commented:
Hi,

I think I am nearer to the solution

Sub Doit()

For Each cell In ActiveSheet.UsedRange
    If InStr(1, cell.Text, ",") Then
        cell.Formula = Replace(cell.Text, ",", "")
    End If
    cell.Formula = Trim(cell.Text)
    a = cell.Text
    ActiveSheet.Hyperlinks.Add cell, "mailto:" & cell.Text
Next ' cell

End Sub

Open in new window

Regards
0
 
llawrencegAuthor Commented:
Rgonzo1971:
I think we have it to the point I can finish it. Great learning experience for me.
Thank you
0
 
llawrencegAuthor Commented:
Great work  Thanks for the lesson as well
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now