If another cell contains just any two characters then make this cell equal...

[joezeppy]

Hi,

I know this should be easy... TIA...

I'm in cell c2… if cell c3 contains just any two characters only (a-z or 0-9) then make this cell equal "." (just a period [or any char I choose]), otherwise just leave cell c2 blank.

 

[Rick Rothstein]

I'm in cell c2… if cell c3 contains just any two characters only (a-z or 0-9) then make this cell equal "." (just a period [or any char I choose]), otherwise just leave cell c2 blank.

Seems like there should be a more compact formula available, but until someone posts one, this should work for you...

=IF(AND(LEN(C3)=2,NOT(ISERROR(SEARCH(MID(C3,{1,2},1),"abcdefghijklmnopqrstuvwxyz0123456789")))),"X","")

Note: I did not think a period stood out all that much... almost could not see it was there... so I used an "X" (shown in red) which you can, of course, change to whatever character you want.

 

[joezeppy]

Hi Rick,

Thanks for this attempt, but it seems not to be working properly. It looks to make perfect sense from the little I do know about formulas. It's giving erratic results. I'm not sure why. Any ideas appreciated.

Below is a link to a screen shot. You'll see that there are (c column) words with many more characters and b column is still showing the X (good idea on the X, thanks). In that image all the cells in b column have the code you gave me pasted in them.

10.20.2013-07.56.10 - joezeppy's library

 

[Rick Rothstein]

Hi Rick,

Thanks for this attempt, but it seems not to be working properly. It looks to make perfect sense from the little I do know about formulas. It's giving erratic results. I'm not sure why. Any ideas appreciated.

Below is a link to a screen shot. You'll see that there are (c column) words with many more characters and b column is still showing the X (good idea on the X, thanks). In that image all the cells in b column have the code you gave me pasted in them.

10.20.2013-07.56.10 - joezeppy's library

The number of X's in Column B are correct, they are just offset 2 rows from where they should be. That tells me you put the original I posted in B6, not B3 (note the formula referenced C3... you were supposed to put the formula in that same row) and copied it up and down from there. HOWEVER, your first piece of data is in Row 4, not 3 as your original post indicated. So, put this formula in C4 and copy it down...

=IF(AND(LEN(C4)=2,NOT(ISERROR(SEARCH(MID(C4,{1,2},1),"abcdefghijklmnopqrstuvwxyz0123456789")))),"X","")

 

[joezeppy]

Duh, You hit the nail on the head and it seems to be working perfect once I corrected that. Thank you so much for taking the time. It works!

 

[Hercules1946]

Hi,
Im glad you found a solution. I spent some time on this on the understanding from your OP that a formula returning "" or 'MyCharacter' would be placed in C2 (based on testing the contents of C3). I assumed that column c would then continue down with formula,data,formula,data etc etc. I suppose this illustrates the problem of not being able to post an example spreadsheet. Not to worry ......

 

[mikerickson]

Its a little more compact, but I'd think before calling it "better".

=REPT(".", (LEN(D7)=2)*ISODD(PRODUCT(MATCH(CODE(UPPER(MID(D7,{1,2},1))),{0;48;57;90;91}))))

 

[joezeppy]

Mike and Hercules, Thanks very much for your ideas too!
Joe