Excel Magic Trick 311: Verify ID prefix w OR function 3 Examples

[MrHelal123]

=HYPERLINK("[Book1]'"&A2&"'!A1­",A2)

Mike can you explain why there are single quotes around this formula

[ExcelIsFun]

single quotes are only requires when there are sapces in the names.

If sheet name is rad, you don't need quotes.

If sheet name is rad cool, you need quotes:

'rad cool'

[MrHelal123]

Thanks Mike

In the hyperlink formula, the quototaion marks and ampersand baffles me a bit. im not sure exactly when to use the ampersand and when to use the quotation marks.

The formula works fine but im just trying to figure out when to use quotation marks and ampersand to join.

[ExcelIsFun]

The single quotation mark is for when there is a space in a name. The ampersand is for when you want to join items.

[MrHelal123]

Hi Mike,

in this example to prefix was always 3 characters thefore we alway told the left function to take 3 characters from the left. What if the prefix varied (i.e some prefixes were 3 or 4 or 5) etc... how can i use the same example not only just look at 3 characters from the left.

[ExcelIsFun]

You would have to find a pattern to exploint, like a dash or something, then use the SEARCH or FIND function.