Search the charactor by space

[sophea_tum]

Hi,

i am working on the short data and i get stuck on sth like the following example.

DATA
adfd dfgfer cdd ffgggg
aa sdd sdsdf d
dfs dfdf dfcd

I have to search cell by cell of the criteria (" yyy " we spell "space + 1st character + 2nd character + 3rd characte + space")
so how can i find the position space (search function) of " yyy " where "y" is the variable single character.

thanks!

 

[Aladin Akyurek]

Nothing said about what to return when successful...

A pattern like " yyy " as you symbolize it, can be rendered as " ??? " in Excel, where ? stands for a single alphabetic token (length = 1).

Row\Col	A​	B​
1​
2​	adfd dfgfer cdd ffgggg	cdd
3​	aa sdd sdsdf d	sdd
4​	dfs dfdf dfcd	dfs
5​

B2, copied down:

=IF(ISNUMBER(SEARCH(" ??? "," "&A2&" ")),
   TRIM(MID(" "&A2&" ",SEARCH(" ??? "," "&A2&" "),LEN(" ??? "))),
   "not found")

 

[sophea_tum]

Nothing said about what to return when successful...

A pattern like " yyy " as you symbolize it, can be rendered as " ??? " in Excel, where ? stands for a single alphabetic token (length = 1).

Row\Col	A​	B​
1​
2​	adfd dfgfer cdd ffgggg	cdd
3​	aa sdd sdsdf d	sdd
4​	dfs dfdf dfcd	dfs
5​

<tbody>
</tbody>

B2, copied down:

=IF(ISNUMBER(SEARCH(" ??? "," "&A2&" ")),
   TRIM(MID(" "&A2&" ",SEARCH(" ??? "," "&A2&" "),LEN(" ??? "))),
   "not found")

HI,

Thanks, that is helpful.

 

[Aladin Akyurek]

HI,

Thanks, that is helpful.

You are welcome.

If you want flexibility...

=IF(ISNUMBER(SEARCH(" "&REPT("?",B$1)&" "," "&A2&" ")),
    TRIM(MID(" "&A2&" ",SEARCH(" "&REPT("?",B$1)&" "," "&A2&" "),
    LEN(" "&REPT("?",B$1)&" "))),"not found")

where B1 houses a value like 3.