# formula to detect specific charachter IF there are no spaces

#### visitor2016

##### New Member
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

Visitor

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
maybe something like...

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

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

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

Last edited:
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

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

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

Try this:

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

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

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

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

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

Replies
2
Views
274
Replies
2
Views
319
Replies
0
Views
167
Replies
21
Views
641
Replies
4
Views
243

1,196,080
Messages
6,013,314
Members
441,760
Latest member
Sharina

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back