Forum home » Delegate support and help forum » Microsoft Excel Training and help » If statements
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
If statements
Resolved · Low Priority · Version 2007
Reply Wed 1 Jun 2011, 16:23Delegate Maaike said...
Maaike has attended:
Excel Advanced course
If statements
I would like to create a form in which special characters such as % and £ can be detected and replaced with 'percent' and 'pound', how is this possible?
For upcoming training course dates see: Pricing & availability
Reply Thu 2 Jun 2011, 12:01Delegate Chris said...
RE: if statements
Type something like "5%" in A1, and then the following in any cell (e.g. A2):
=IF(ISERROR(FIND("%",TEXT(A1,"0%"))),TEXT(A1,"0%"),SUBSTITUTE(TEXT(A1,"0%"),"%"," percent"))
You should be able to adapt it from there.
Hope this helps! :)
Chris Martin
Senior Analyst
Research Partnership
RE: if statements
Hi Maaike, thanks for your query. That's going to be difficult to achieve within the same control on a form, I'm afraid - particularly if the form you are building is on the worksheet itself. Essentially you're allowing the user to change a cell, but including code in that cell to parse the incoming text and format it accordingly. You could do this with a mixture of IF and RIGHT functions, but only to send the end result into a different cell.
Alternatively you could code the whole thing, but that's a conversation for our VBA course!
Hope this helps,
Anthony
Thu 9 Jun 2011: Automatically marked as resolved.
|