Solved

delete all formatting after last cell character

Posted on 2013-06-12
7
313 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 51

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 51

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 51

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This collection of functions covers all the normal rounding methods of just about any numeric value.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 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