My issue/Question is:
The scenario:
An operator is keying in data and comes to this field to enter either/both alpha/numeric characters and found that an error was made in entering the data.
Using a space key to clear the data in cell A1.
=IF(A1="","del key","space key") the result is False = Space key is shown
Using the del key to clear the data in cell A1:
=IF(A1="","del key","space key") the result is True = del key is shown
Conclusion is, the operator is not aware of the action of the formula in either true or false condition function as a result of using either space or delete key To clear the field.
I guess in order to resolve the issue you would have to nest the CHAR function with the If Function in order to resolve the space/delete issue.
I have made some attempt to use the char function with the if function and my attempt is not successful
Perhaps you would formulate the solution for me. I would be very appreciative for your assistance in the solution. I would prefer an email with the solution.
Thank you and have a great day.
Forrest aka effjamis
Hi Forrest
Try this
=IF(AND(ISBLANK(A1)=FALSE,LEN(SUBSTITUTE(A1," ",""))=0),"Space Key","")
Sunny
SunnyKow said
Hi ForrestTry this
=IF(AND(ISBLANK(A1)=FALSE,LEN(SUBSTITUTE(A1," ",""))=0),"Space Key","")
Sunny
Sorry Sunny, I am a novice and I am having a problem with your solution. I guess I do not understand where yours will fit into mine. would you mind to incorporate your formula with mine.
Using a space key to clear the data in cell A1.
=IF(A1="","del key","space key") the result is False = Space key is shown
Using the del key to clear the data in cell A1:
=IF(A1="","del key","space key") the result is True = del key is shown
Thank you
Try this
=IF(ISBLANK(A1),"Del Key",IF(LEN(SUBSTITUTE(A1," ",""))=0,"Space Key",""))
It will show Del Key if A1 is deleted with the DELETE key
It will show Space Key if A1 is deleted with the SPACE BAR (irrespective of how many times the space bar is pressed)
It will show a blank if there is a value in A1
Hope this helps
Sunny
Sunny
Ah Ha.... that looks good. I will give that a go today.
Thank you so much.
You have a great week end.
Forrest
Hi Forrest
You have a good weekend too.
Do let us know the outcome.
Sunny
Hi,
As usual with Excel, there is more than one way you can do it to get what you want. The result is the same as what Sunny already gave you, just to show you there are more than one way to solve things.
=IF(ISBLANK(A1),"Del Key",IF(CODE(A1)=32,"Space Key","Data exist"))
For characters above code 255 you need to use UNICODE function.
You can read more about the character codes here --> https://www.myonlinetraininghub.com/alt-codes-symbols-characters
Br,
Anders
I had use SUBSTITUTE to cater for all scenarios (I hope I got it all covered)
Just in case there is a SPACE in front of a text in cell A1 eg. " Hello" (without the quotes)
This scenario (in theory) should not occur if the operator delete all the text in cell A1 using either method.
Cheers
Sunny
