Solved

delete all formatting after last cell character

Posted on 2013-06-12
7
310 Views
Last Modified: 2013-06-14
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
Comment
Question by:llawrenceg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39243701
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
 

Author Comment

by:llawrenceg
ID: 39244293
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
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39244523
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:llawrenceg
ID: 39244721
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
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39246679
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
 

Author Comment

by:llawrenceg
ID: 39247707
Rgonzo1971:
I think we have it to the point I can finish it. Great learning experience for me.
Thank you
0
 

Author Closing Comment

by:llawrenceg
ID: 39247713
Great work  Thanks for the lesson as well
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question