Verifying 5th character from the end of text string by searching from right to left

[Pneumafire]

I am using Excel 2010.

I have text strings between 30 and 40 characters in length. Let's say I have a 40-character text string in a cell, with the last 6 characters being 7 1210

What I need to do is to verify if the character between the 7 and the 1 is a space.

The formula that I need would be the equivalent of "If the fifth character from the right = " ", then do this..."

I don't want to search from the left unless I have to because there are at least 5 spaces in every string, but I am only interested in the fifth character from the end of the string. If it is a space, then I will take one action; if it is not, then I will take another.

Thanks in advance to you pros that can solve tough puzzles like this one!

 

[mikerickson]

You use a formula like this
=CODE(RIGHT(A1,5))=CODE(" ")

or (less genric)
=CODE(RIGHT(A1,5))=32

 

[Pneumafire]

Perfect! Well done, Mike. Thanks for the quick reply, and thanks for two solutions - they both work beautifully and accomplish exactly what I needed. Again, big KUDOS to you.

 

[Pneumafire]

Mike, FYI, Excel 2010 likes CODE 160 (which is also a blank space) over 32, at least with the default font Calibri.

 

[L. Howard]

Here's another, bit longer tho.

=IF(--RIGHT(F1,4)=--RIGHT(F1,5),"Space","No Space")

Regards,
Howard

 

[Pneumafire]

Thanks, Howard - I'll try it out. Always good to know of other options.