formula to detect specific charachter IF there are no spaces

[visitor2016]

Hello,

I am looking for a formula to determine if a cell contents has a specific charachter, in this case a - (hyphen/minus) but only without any spaces before and after


John-Doe = TRUE
John - Doe = FALSE


Thanks for your help


Visitor

 

[Weazel]

maybe something like...

=ISERROR(SEARCH(" - ",A2))

 

[FDibbins]

Will there be a space both sides?
=ISERROR(FIND(" - ",A1))
or just 1 side?
=ISERROR(OR(FIND("- ",A3),FIND(" -",A3)))

 

[AliGW]

The solutions offered so far will also return TRUE if the cell contains JohnDoe.

 

[visitor2016]

ADVERTISEMENT

Hello,

I am looking for a formula to determine if a cell contents has a specific charachter, in this case a - (hyphen/minus) but only without any spaces before and after


John-Doe = TRUE
John - Doe = FALSE


Thanks for your help


Visitor

thanks for your response. it did work. but I really need to solve the folowing:


I have in column A the following text in the first 2 lines:


M. JOHN DOE - 304 MAIN STREET
M. JOHN-DOE - 304 MAIN STREET


I need to have in column B a formula that i can drop-down, that will extract only the text before the " - " (hyphen with spaces), and in column C a formula that will extract only the text AFTER the " - " (hyphen with spaces) while ignoring any hyphens which are WITHOUT spaces before and after the hyphen. So that it provides the same result for line 1 and line 2

 

[mole999]

if you return "- Doe" and " Doe" you can use TRIM to remove the spaces from either

 

[AliGW]

ADVERTISEMENT

Try this:

B1: =LEFT(A1,FIND(" - ",A1))

C1: =RIGHT(A1,LEN(A1)-(FIND(" - ",A1)+2))

 

[visitor2016]

ok that worked, now what can I put in D1, E1 and F1 :

M. JOHN DOE - 304 MAIN STREET - APT 9 - 33021 - HOLLYWOOD
M. JOHN-DOE - 304 MAIN STREET - APT 9 - 33021 - HOLLYWOOD
M. JOHN DOE - 304 MAIN STREET - APT 9 - DOOR 7 - 33021 - HOLLYWOOD
M. JOHN-DOE - 304 MAIN STREET - 33021 - HOLLYWOOD


I want as follows:

B1 = first and last name
C1 = address line 1 (only street number and strteet name)
D1 = adress line 2 (everything else after the street name, before the zip code, like " APT 9 - DOOR 7")
E1 = zip code
F1 = city

 

[AliGW]

Why not use text to columns with " - " as the separator?

 

[Sektor]

With VBA:

Function CheckCell(cell)
    Dim re As New RegExp
    re.Pattern = "\S-\S"
    FFF = re.Test(cell.Value)
End Function