Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I remove prefix apostrophe
Hi,
I have a spreadsheet that is exported from a database, and all of the cells have an apostrophe prefix. The apostrophe keeps hyperlinks from functioning (since they are now considered text). Here is the code I have created to remove the apostrophe - unfortunately it removes the first actual character in the cell as well as the apostrophe: Public Sub ParseApostrophe1() Dim Cell As Range Worksheets("customers").Activate ActiveCell.CurrentRegion.Select For Each Cell In Selection If Len(Cell.Value) = 1 And Cell.PrefixCharacter = "'" Then Cell.Value = Right(Cell, Len(Cell) - 1) End If Next Cell End Sub Does anyone have an idea how I can get rid of the apostrophe without deleting the first actual (visual) character in the cell. TIA Best regards, Bill |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I remove prefix apostrophe
I have not tried to write a procedure to do what you are asking, but I have used the search & replace from within the spreadsheet to replace everything matching the search criteria. I don't know if any of your hyperlinks have an apostrophe in the address itself. Wouldn't seem likely? Make a backup copy before your do try this. -- DCSwearingen ------------------------------------------------------------------------ DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506 View this thread: http://www.excelforum.com/showthread...hreadid=501234 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I remove prefix apostrophe
I'd replace:
Cell.Value = Right(Cell, Len(Cell) - 1) with these lines: with Cell .numberformat = "@" 'keep it text .value = .value end with swansonwc wrote: Hi, I have a spreadsheet that is exported from a database, and all of the cells have an apostrophe prefix. The apostrophe keeps hyperlinks from functioning (since they are now considered text). Here is the code I have created to remove the apostrophe - unfortunately it removes the first actual character in the cell as well as the apostrophe: Public Sub ParseApostrophe1() Dim Cell As Range Worksheets("customers").Activate ActiveCell.CurrentRegion.Select For Each Cell In Selection If Len(Cell.Value) = 1 And Cell.PrefixCharacter = "'" Then Cell.Value = Right(Cell, Len(Cell) - 1) End If Next Cell End Sub Does anyone have an idea how I can get rid of the apostrophe without deleting the first actual (visual) character in the cell. TIA Best regards, Bill -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I remove prefix apostrophe
Dave,
That worked perfectly. Thank you for your help. Best regards, Bill "Dave Peterson" wrote: I'd replace: Cell.Value = Right(Cell, Len(Cell) - 1) with these lines: with Cell .numberformat = "@" 'keep it text .value = .value end with swansonwc wrote: Hi, I have a spreadsheet that is exported from a database, and all of the cells have an apostrophe prefix. The apostrophe keeps hyperlinks from functioning (since they are now considered text). Here is the code I have created to remove the apostrophe - unfortunately it removes the first actual character in the cell as well as the apostrophe: Public Sub ParseApostrophe1() Dim Cell As Range Worksheets("customers").Activate ActiveCell.CurrentRegion.Select For Each Cell In Selection If Len(Cell.Value) = 1 And Cell.PrefixCharacter = "'" Then Cell.Value = Right(Cell, Len(Cell) - 1) End If Next Cell End Sub Does anyone have an idea how I can get rid of the apostrophe without deleting the first actual (visual) character in the cell. TIA Best regards, Bill -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove Apostrophe from end of number | Excel Discussion (Misc queries) | |||
Remove Trailing Apostrophe | Excel Discussion (Misc queries) | |||
how to remove the apostrophe | Excel Discussion (Misc queries) | |||
'09:00AM trying to get remove the apostrophe | Excel Discussion (Misc queries) | |||
Remove apostrophe | Excel Worksheet Functions |