Asterisk - in formula, big problem

[Bilingual]

Hi , i have a lot of address strings where i used LEFT to separate the first field in another column, as all adress strings which begins with special characters needs to be grouped.

As asterisk is a wild card it creates huge problems, i can easily separate the character but my lookup formulas does not work with it, other special characters are fine:


Example

*SP.SKOLEN BRAMSNÆSVIG

I need to separate the Asterisk in one row and look it up in another table, but it does not find it, any solutions?

 

[Andrew Poulsom]

You need to escape the wildcard by preceding it with a tilde (~). Does this help you?

Excel 2010
	A	B	C	D	E
1	*SP.SKOLEN BRAMSNSVIG	Wrong	A	1SP.SKOLEN BRAMSNSVIG	A
2		Right	C	2SP.SKOLEN BRAMSNSVIG	B
3				*SP.SKOLEN BRAMSNSVIG	C
4				4SP.SKOLEN BRAMSNSVIG	D
5				5SP.SKOLEN BRAMSNSVIG	E
Sheet1

Cell Formulas
Range	Formula
C1	=VLOOKUP(A1,D1:E5,2,FALSE)
C2	=VLOOKUP(SUBSTITUTE(A1,"*","~*"),D1:E5,2,FALSE)

 

[FormR]

Hi, you need to use the tidal (~) character to escape the wild card.

Something like:

Excel Workbook
	A	B	C	D	E
2	}	Something		*	SP.SKOLEN BRAMSNSVIG
3	*	SP.SKOLEN BRAMSNSVIG
4	[	Another Thing
Sheet1