Email:      Pass:    Pass?
Close Window   
Subscribe for Email Updates!
Excel tips, help, and more!


Free Excel Forum

Adding A Prefix To A Cell By Using A Formula

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Is there a formula that I can apply to a specific cell, so that when
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

Similar Excel Video Tutorials

Helpful Excel Macros

Display The Actual Link / Email Address From Links in Excel - UDF
- Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the ac
Email Current Workbook & or Other Attachments
- This macro will send the current workbook in an email through Microsoft Outlook. The Macro allows you to send the most
Send Emails through Outlook using Email Addresses from Excel and text from Word
- This macro allows you to send an email to a list of recipients through excel. The email will be sent through Outlook an
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Open Microsoft Outlook from Excel
- This free macro will open the Microsoft Outlook program on your computer. You do need to have this program first. This

Similar Topics

When typing an email address in a cell a hyperlink was automatically set to
that email address. When updating or changing the address the hyperlink
stays linked to the old address and the edit hyperlink selection when right
clicking the address is unavailable although the open hyperlink is available.

hello all,

I would like to add an email hyperlink into a cell, but I only want the email address to appear as the blue link, the way Im doing it now, all the text in the cell appears as a hyperlink, any solutions to this problem?

also, how do I add cc's so they are automatically typed in when I click on the email hyperlink?

Thank you sincerely,


I have a spreadsheet where I paste email address that I copy from websites, and when I do so excel automatically makes the cell an active hyperlink.

Per required usage of the spread sheet I later copy over the email address with a more current email address, but its does not remain a hyperlink.

Most importantly the original hyperlink when clicked on opens my email program and inserts the subject line automatically from the cell directly to its left. This is what I want the pasted email to do, but insted it shows up as text with no clickable link.

I know how to make it a hyperlink, but I only know how to do this by manually typing the subject and email address into into excel. I would like to be able to paste the new email address and keep it a hyperlink with a reference to the cell to its left for the subject line, or find some way to fill the hyperlink format from other cells.

If you can solve this you will be my personal hero. And that means if you are reading this you have made it to the bottom of this post, so thanks that there are people in the world like you.

In any cell.

When I type a eMail address, such as: " " into any cell and then touch enter, it automatically becomes a hyperlink coloring it blue and underscored.

I do not want it to be a hyperlink, for when it is a hyperlink, I have to click on remove hyperlink.

Anyone have a suggestion?

I'm using the formula below to automatically select the hyperlink location.


It works great with named ranges, but I want to get crazy and have the hyperlink result be an actual web address versus a defined range name. The result I'm getting is "Reference is not Valid"

I've used both the http:// prefix and only www. prefix. Neither worked. Any help would be greatly appreciated.

I copied a table from web site to excel ehich has the email address as
hyperlink to names of people. In Excel it appears as hyperlink and shoes the
name, and email address as hyperlink value.
How can I extract the hyperlink value and have the acctual email address on
another cell.

Thanks in advance

Hi all,

I have made a form for other non-PC literate people to complete and email to me.

I need to make it as easy as possible, and want to add a button which, when clicked, will automatically attach and send the form to my email address.

I have got as far as either a macro to attach it to an email, or a hyperlink to send an email to my address, but I can't get both actions to work at the same time.

Anybody help?

I am trying to insert a hyperlink in cell C1 for an email address. Is it possible to have the hyperlink mailto address be based on a cell. For example, if cell A1 is a name cell (John Smith), and I know the email address is, can Excel automatically assign the email address in the hyperlink in cell C1 to or whatever name is in cell A1? Thanks for any help.

Hi there. I have a big question that i cant figure out and hopefully i can explain this enough for you to understand.

I have an excel spreadsheet with email address on it. There are two columns the first column says the persons name and the second column says "email person" (EP). So the second column doesnt list the actually email address, it says EP and it is a hyperlink. If you scroll over the hyperlink it does show where the email will be sent if you press EP. I am wondering if there is a way to somehow edit the hyperlink so that it shows the actually email address instead of the hyperlinked words EP. I know you can go edit each name individual and change the text in the box field but i dont want to do it one by one. I have over 20k names so it would take forever. Is there a way to do it all at once? some formula or trick that would do it? Any help would be great. Thanks

I am using this formula to pull an email address from cell A1 : =HYPERLINK("mailto:",A1)

A1 contains the formula to create the email address : =HYPERLINK(IF($X$8>0,(CONCATENATE($X$8,"")),""))

H4, the destination cell, shows the email address in the correct format & is hyperlinked. However when I click the cell, a new mail message opens, but the email address does not appear in the TO box.

Any ideas?


I'm using a VLookup to insert an email address into a spreadsheet, and I
would like the hyperlink to work.

This is my formula

=IF(ISBLANK($A6),"",IF(ISBLANK(VLOOKUP($A6,applicant,14,FALSE)),"",hyperlink("mailto:"&VLOOKUP($A6,a pplicant,14,FALSE))))

This gives me the mailto:emailaddress but it is not clickable. However, if
I then paste (special) the resulting values of the formula to another cell it

The original cell with the e-mail address in (in the vlookup table) links

I have noticed that when using the Hyperlink function (not nested), the
address needs to be in quotation marks. Could this be my problem here? If
so, is there anything I can add to my formula which would enclose the
hyperlink bit in quotation marks and make it work?

Any ideas?

Many thanks,


I have a form that I have created in Excel. I would like to add a hyperlink into the form so that once the form is complete, clicking on the hyperlink will go directly to an Outlook email screen and input the excel document into the email ready for sending.

Can anyone help with how to do this? I have got the hyerplink bit set up to go straight to the email and input the correct email address but am struggling with how to add the form as an attachment.


Does anyone know how to disable the feature that automatically converts an email address in excel to a hyperlink when I hit enter to leave the cell. I don't want it to change to a hyperlink because it changes the font and size making me manually remove it and change the font and size.


Hey guys I wouldn't consider myself a novice excel user, although this question is making me feel like one!

I am just using a simple sheet to keep track of email addresses. Every time I enter an email address it automatically creates it as a hyperlink/mailto function. So if I click on the cell, it will create a new email via outlook addressed to that person.

This is driving me nuts! I have a few thousand emails, mostly copied from other people's sheets, and 90% of them are set like this. Is there a fast way I can disable this function so that every cell is just text?



I have an excel sheet that is a list of names and email addresses.

I would like to click on a email address hyperlink and for a vb macro to open a new email message using a specific template i have done. The macro should fill in the address clicked in to the To: field of the email.

Can anyone help me with this as I am new to VB.


Hey Guys here is what im trying to do
I am adding a hyperlink to a sheet on a workbook...
this hyperlink is going to open a email address with the "to" and subject already filled out BUT
I want the sheet to also be attahced to this email automatically.
any ideas?

I have a cell that takes the first and last name of each employee and creates
thier email address. How can i add a command to the following formula to
convert this cell to a hyperlink email cell.


A1 = Joe
B2 = Smith
C3 =
(I want C3 to be dynamic so when I click on it, outlook opens and persons
name is in the to: field. )

It works if I just type C3 out in any cell, but not when the cell is

Hi, I have data in a row that I want to email when I click on a cell (I have the formula worked out for this)
Basically I want to be able to select from a dropdown (in Cell I2) a certain email address, then click "email data" and have the email address populate in my mail client (lotus)
attached is a sample file, for reference the validation list is in I2


=HYPERLINK("'s the data%0AData1=" & A2 & ", Data2=" & B2 & ",Data 3=" & C2 & ",Data 4=" & D2 & ",Data 5=" & E2  & ",Data 6=" & F2,"Email data")

Anyone able to help?

I have a form with a drop down list of different departments. When they select a department, that deptartment's email address populates another cell. (I used an "IF" and "Vlookup" statement for that).

I also have a command button that they can click to email the form as an attachment. The only problem is that it wants them to enter the email address manually. I want to populate the "Send To" in the email to whatever is listed in the email reference cell.

For example, if they choose Business Support from the drop down box, then an email address will populate in cell G18 ( I then want them to click on the "Email" Button and it bring up the outlook dialog box with the email address already typed in the "TO:" field. Hope this makes sense.


Please help me on this I am beginner to VBA .
Learning Forms
so In an Excel VBA form there are few fields like Date, Prefix, Full name....

For Date I Want That Date When We Will Open The Form
For Segment I Have 3 Option 1 - Government School, Private / Public School, Special Category
Prefix Should Be Only - Mr., Mrs., Dr., Ms. & Prof., Father, Sis.
Gender Should Filled Automatically After Filling Prefix
and other fields are simple Entry
After completing the form all data should be pasted into excel sheet in this given format on clicking Enter button. 1 more thing I want is that the name should split in three column 1st name, 2nd name, 3rd name.

other format is given below -


* A B C D E F G H I J K L 1 Date Registration Segment Prefix Name of teacher Gender Date of Birth Address 1 Address 2 Address 3 City Pincode State

Excel tables to the web >> Excel Jeanie HTML 4

I Hope u all understand.

Thanks in advance

I use a pivot table to create accounts receivable statements for hundreds of my suppliers. I have email addresses on a seperate sheet in my workbook.
When I choose a specific account in the pivot table, I am able, using a vlookup, to reference this email address. I now want to turn this into a hyperlink that will open my email client with the appropriate mailto address.

I have tried to use a cell reference inside a hyperlink function, but no luck...

I have a formula which uses the following Function to Replace text.

This pulls out the prefix of a tag.


Function Prefix(txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "_\D+"
    Prefix = Mid$(.Execute(txt)(0), 2)
End With
End Function

ie Cell A1 = PLANT_PMP2345_cSTRT
Cell B1 = Prefix (A1) - Result = PMP

What i need to do with this is make the Cell C1 = PLANT_PMPx2345_cSTRT
At present i use this formula :
HTML Code:


Is there any way i could do this as a function without having to refer to Cell B1.


I have created an address list in Excel where I type an address in a cell in Column A, using Alt Enter for each line of the address. When I finish the address, I click into the next Cell (A2, A3, A4, etc.) My hyperlinks (each person whose address I've typed in has an e-mail address) to each addressee's e-mail address do not work. I have tried and tried various things to get the e-mail addresses to "hyperlink", but to no avail. Excel "greys" out the Hyperlink button, so I can't use that. Is it because I'm using "Alt Enter" when typing in each line of an address in the cell? Please help!

I have copied a web page into EXCEL that contains numerous hyperlinks labeled
"e-mail". When I click on the hyperlink Outlook opens and the e-mail address
is populated on the "to:" line. I want copy the underlying e-mail address in
the adjacent cell, but can not find a way to do it (all I get is another cell
labeled "e-mail"). I can do it by right clicking on the cell and selecting
"edit hyperlink" and do a copy/paste w/ the e-mail address. However, I have
a ton to do and was hoping to find a quick way to copy the email address,
As an example, The cell (c4) reads "E-mail" & when the cursor is pointed at
the label the underlying hyper link is displayed (eg. I want to get the underlying email address in the
cell next to C4 (i.e. E4 would read All help is


Hi all,

I use excel as a front end to produce hundreds of dashboards which are then emailed out to staff.

There are two hyperlinks on the top of each dashboard - one is a mailto with my project's email address, the other is a hyperlink to an interactive powerpoint show (pps) in kiosk mode which works as a help file.

The pps sits on a shared drive which everybody has read access to.

The hyperlink works fine locally, but not when emailed out.

This is because excel insists on turning my hyperlink into a relative one rather than absolute, so when the dashboard is launched from a user's email system, the link no longer points at the file.

I need the hyperlink to be absolute, but can't find out a way of setting this for the link or for the workbook.

Changing the computer settings will not be an option as I'd have to do it for all 400 recipients.

Anybody got any bright ideas?