Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

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

Apply Conditional Formatting to Multiple Cells with a Single Formula
How to use a single formula to apply conditional formatting to multiple cells at once in Excel. This saves you the ...
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 ...
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 t ...
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 ...

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.

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


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?



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 ;-)


Please help me with a question:

I would like to be able to convert email address hyperlinks to the actual email text.

For example, let's say Alex Rodriguez's email address is If I have a hyperlink in an excel cell that reads "Alex Rodriguez" how do I convert it to read the actual email address in that cell that the hyperlink sends me to which is

Further, if i have a list of 100 such hyperlinks, how can I convert them all in one swoop?

Thanks in advance.

Hi All,

When we enter an email address in Excel or any other MS app, it automatically configures it with a hyperlink to the resident email system. In my case Outlook.

Is it possible, when clicking on the email address, to trap the process so that I can automatically add data to the email.



when I type an email address it should be recognized, but about 1/3 of the
time it does and the rest it does not. If it does not and I right click on
the cell "hyperlink" is greyed out. Any suggestions to solving this problem?

From the text of an email body that looks similar to this

LastName: Doe
FirstName: John

email: johndoe

cell: 555-555-5555
end: Please Send me a Tutorial PDF

I am able to extract the Last Name, First Name and Cell number
the problem is when I extract the email address this is what I get;

Is there a function that removes the hyperlinks, so only the actual email address remains in the string?


I am currently using the following code to send my excel workbook in an email.

*ActiveWorkbook.SendMail Recipients:=""

But I have to write an email address in the code. I would like it to be picked up from a cell within the worksheet. I have also tried

*ActiveWorkbook.SendMail Recipients:=range("A1").select (This cell contains the email address where the email needs to go) but it is not working. Can I get some suggestions on how to make it work please so that I enter an email address in the sheet and use the macro to pick that email address automatically.

I will appreciate your help.