Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & 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

View Answers     

Similar Excel Tutorials

3D References - Formulas that Reference Multiple Sheets at Once in Excel
Have one simple formula that will reference the same cell or range of cells on multiple worksheets at once without ...
How to Add, Remove, and Rearrange Columns and Rows in Excel
Adding, removing and rearranging columns is as easy as resizing. You just select your columns or rows, right click ...
How to Add Boxes, Buttons, Arrows, Speech-Bubbles, Hearts, and More to a Spreadsheet in Excel
In this tutorial I am going to cover inserting and editing Shapes in an Excel workbook, as well as adding text to t ...
Link to Cells on Other Worksheets in Excel
It is very easy to link one cell to another cell in Microsoft Excel. The steps needed are listed below as well as p ...

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
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

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?

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 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.



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,


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?


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 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.


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 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 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?

Good Afternoon,
Can someone please show the code for a problem I am having. I am trying to send an email automatically when a button is pressed on a form. I have tried

Sub roundedRectangle4_Click()
ActiveWorkbook.SendMail Recipients:="name email", Subject:="Event"

This bit is Ok, but when I try to add the hyperlink in Cell e12 it does not work. The hyperlink changes from time to time, so it really just needs to be the contents of cell E12.

Many thanks in anticipation.


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!

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?



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.


Hi all,

I made a userform that looks like a database for contacts.
I have an email address in a textbox, but I would like to click a button and have it open outlook new email form.
When you put an email address in excel cell, it becomes an hyperlink. I just can't have this done on the userform. Is there a way to do this? After goggling around I wasn't able to find nothing helpful at all.

Hope some one can help out ;-)