How to count if dash in string has n spaces?

Magnatolia

Board Regular
Joined
Jan 19, 2012
Messages
81
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

Is it possible to count the number of dashes in a string that do not have space on both or just one side? The string length will be inconsistent so cannot use number of words. e.g. "This-document" would be counted, whereas "This - Document" would not.

Thanks!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Is this what you want?

Book1
ABCD
1This-document1
2This - Document0
3This--document2
Sheet1
Cell Formulas
RangeFormula
D1=LEN(A1)-LEN(IF(ISERROR(SEARCH(" - ",A1)),SUBSTITUTE(A1,"-",""),A1))
D2=LEN(A2)-LEN(IF(ISERROR(SEARCH(" - ",A2)),SUBSTITUTE(A2,"-",""),A2))
D3=LEN(A3)-LEN(IF(ISERROR(SEARCH(" - ",A3)),SUBSTITUTE(A3,"-",""),A3))
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,959
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Is this what you want?
ABCD
1This-document1
2This - Document0
3This--document2

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
D1=LEN(A1)-LEN(IF(ISERROR(SEARCH(" - ",A1)),SUBSTITUTE(A1,"-",""),A1))
D2=LEN(A2)-LEN(IF(ISERROR(SEARCH(" - ",A2)),SUBSTITUTE(A2,"-",""),A2))
D3=LEN(A3)-LEN(IF(ISERROR(SEARCH(" - ",A3)),SUBSTITUTE(A3,"-",""),A3))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Scott, your formulas do not take into account the red highlighted part below of the OP's question...

"....in a string that do not have space on both or just one side?"
 
Last edited:

Magnatolia

Board Regular
Joined
Jan 19, 2012
Messages
81
Office Version
  1. 365
Platform
  1. Windows
Is this what you want?
ABCD
1This-document1
2This - Document0
3This--document2

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
D1=LEN(A1)-LEN(IF(ISERROR(SEARCH(" - ",A1)),SUBSTITUTE(A1,"-",""),A1))
D2=LEN(A2)-LEN(IF(ISERROR(SEARCH(" - ",A2)),SUBSTITUTE(A2,"-",""),A2))
D3=LEN(A3)-LEN(IF(ISERROR(SEARCH(" - ",A3)),SUBSTITUTE(A3,"-",""),A3))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Thanks! This is awesome, however I think this only works if there is one dash. What would I need to change if I wanted to check any dash in the string? e.g. TEAM - DOCUMENT TYPE -TITLE

Thanks!
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,832
Office Version
  1. 365
Platform
  1. Windows
Maybe this:
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">A-B -CC- D-E - F</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A-B -C-C- D-E - F</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">asd</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:4,8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=LEN(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">A1,"- ",""</font>)," -",""</font>)</font>)-LEN(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">A1,"- ",""</font>)," -",""</font>),"-",""</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=LEN(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">A2,"- ",""</font>)," -",""</font>)</font>)-LEN(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">A2,"- ",""</font>)," -",""</font>),"-",""</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=LEN(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">A3,"- ",""</font>)," -",""</font>)</font>)-LEN(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">A3,"- ",""</font>)," -",""</font>),"-",""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,109
Messages
5,857,439
Members
431,879
Latest member
KiwDaWabbit

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top