Force text to be lowercase / copying the results of a formula not the formula itself

I have created the attached spreadsheet which pulls together various pieces of data a user enters and concatenates them into a string.

There are two issues I am trying to fix:

1) It is important that the data which appears in the results cell (B10) is all lowercase. Can I add in a rule that corrects this automatically if someone enters data including uppercase characters?

2) I want the users to be able to copy the resulting data string from B10 into column E of the 'collated' worksheet but have so far been typing it in manually as it tries to copy the formula. Is there a way to amend B10 so I don't have to ask people to use any 'Paste special' function (I am trying to make the instructions as simple as possible).

Thanks

Glayva

Thanks Kaper. Would be interested in the macro solution.

Hi Glayva,

Please enter below formula in column E3 to E5:

="http://bbc.co.uk"&'Link builder'!$A$14&A3&'Link builder'!$A$15&B3&'Link builder'!$A$16&C3&IF(D3="","",'Link builder'!$A$17&D3)

hope this will help

Thanks

Thansk Kaper - but I get a Runtime-error '5'; message when I try to run that macro. It says there is an invalid call or argument and the debugger highlights the following line:

.Hyperlinks.Add Anchor:=.Cells(freerow, "E"), Address:=Range("B10"), TextToDisplay:=Range("B10")

Thanks blueblink01 but this isn't quite what I am looking for. The URL (and all other details) will change every time and I want the link to pull through without having to manually populate columns A-D.