Adding a prefix to a cell by using a formula

[Guest]

Is there a formula that I can apply to a specific cell, so that when
information
it typed into that cell, it will automatically add a set prefix ?

I have a form I designed in Excel that will be emailed to other people. One
of the fields that they need to fill in is an email address. I do not want
this turning into a hyperlink. I know how to disable the hyperlink on the PC
I'm working on , but when the form gets emailed to others, the disable
function is lost. If you type in a " ' " before the email address, it will
not turn into a hyperlink. Is there a way to set the single quotation as a
prefix for that cell, that will automatically apply to the cell after someone
has typed in an email address ? Thank you in advance for you time - MUCH
APPRECIATED !!

 

[Guest]

Have not done this myself, but you might try creating a Custom Format and
applying that formatting to the cells involved.|:>)

 

[Bernie Deitrick]

Audrey,

1) Try pre-formatting the cells as text.

2) Tools > Options... Spelling Tab, click the "Autocorrect Options..."
button, select the "Autoformat as you type" tab, uncheck "internet and
network paths with hyperlinks"
But I think this option is only available in Office XP and up....

HTH,
Bernie
MS Excel MVP

 

[Guest]

If I generate the form in Office XP, do you know if it will
function as I'm hoping on any PC that has different versions of Office - like
older versions ?

 

[Bernie Deitrick]

Audrey,

You would need to test it in each of the Excel versions which it may run
under.

Alternatively, you could use the worksheet's change event to prevent this
behavior. For example, copy the code below, right click the sheet tab of
the sheet where the values are entered, select "View Code" and paste the
code into the window that appears. The example code will prevent hyperlinks
from appearing in cells A1:A100.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myVal As String
If Intersect(Range("A1:A100"), Target) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False
myVal = Target.Value
Application.Undo
Target.Value = "'" & myVal
Application.EnableEvents = True
End Sub