Return Position of Special Character In a String

Question

? is a special character in excel, and as far as I'm aware, won't work with =FIND("?",<stringref>)

=FIND(CHAR(63),<stringref>) returns what I want. In my case it's 11, because the ? character appears first, 11 characters along my string.

Now, I want CHAR(63) to be referenced from another cell in the formula, NOT hard coded.

So let's say cell A1 has my formula, and A1 has my search item, ? or CHAR(63).

How do I reference ? or CHAR(63) from my formula in cell A1 to return the number of characters along my string (in C1)?

Here's my attempt:

=CODE("?") produces "63", so in theory I could use that.

A1 = FIND(B1,C1) = Value Error
B1 = CONCATENATE("CHAR(",CODE("?"),")") = CHAR(63)
C1 = somestring?someparam

I want: A1 = 11

The problem seems to be referencing the string CHAR(63) in another cell, because it works if I embed the formula of B1 into A1.

unclear sorry bro, i still not understand what you looking for — Asaf Shazar, Jan 27 '16 at 23:50
It's in the title. I want to return the position of a `?` character in a string in excel. — square_eyes, Jan 27 '16 at 23:51
such as if like in cell A1 have the word hello?words it should return 6? — Asaf Shazar, Jan 27 '16 at 23:52
Yes that's right. But '?' needs to be in B1 as the lookup value, not hard coded in the A1 formula. — square_eyes, Jan 27 '16 at 23:52
so you looking for **index** of the char '?' in word that what you ask for write it like that, wait i try to help you — Asaf Shazar, Jan 27 '16 at 23:53

score 1 · Accepted Answer · edited May 23 '17 at 11:59

1

You don't need to do all that jazz in cell B1. Just put ? in B1 (with no quotes and no equal sign) and then use the formula,

 =FIND(B1,C1)

in Cell A1.

Note that if you are trying to conceptually figure out how to put "CHAR(63)" into Cell B1 and then have it evaluate this function when using it as an input to the "Find" function, then you would have to use some VBA, such as here.

Additionally, you could put "=code("?")" into cell B1 and then use:

=FIND(CHAR(B1),C1)

in cell A1.

edited May 23 '17 at 11:59

Community

1
1

answered Jan 27 '16 at 23:54

OpiesDad

3,385
2
16
31

Yes I seem to have gone on a tangent and missed this. You are right. I think my top rows of data had no ? hence the failing. When I fill to something with a ? it returns correct:) - Could have saved myself an hour of going down the rabbit hole. – square_eyes Jan 28 '16 at 00:01
I just tested your original premise of "=FIND("?", )" and that also worked for me in Excel 2013. Perhaps it doesn't work in earlier versions – OpiesDad Jan 28 '16 at 00:05

Return Position of Special Character In a String

1 Answers1