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