Search for Text String within Text. Right to Left!

[hazza147]

Hi,

I'm trying to find the position of the last space bar before the number of characters in a cell exceed 30. To do this I need to configure a search function which searchs from right to left. Starting from 30 characters and working backwards to find the location of the next space.

How would I go about doing this!?

Any suggestions would be greatly appreciated.

Thanks,
Harry

 

[Sektor]

Example?

 

[hazza147]

'Hello SekTor. How are you doing?'

The output of the desired formula would be 26 in the example above. As character 26 in the above string is the last spacebar before the string exceeds 30 characters.

 

[Sektor]

[COLOR="Blue"]Function[/COLOR] LastSpace(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    
    [COLOR="Blue"]Dim[/COLOR] mc [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Object[/COLOR]
    
    Application.Volatile
    
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Global = [COLOR="Blue"]True[/COLOR]
        .Pattern = "\s+"
        [COLOR="Blue"]Set[/COLOR] mc = .Execute(Left$(Str, 30))
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
    LastSpace = mc(mc.Count - 1).FirstIndex + 1
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[hazza147]

Thanks very much for this sektor.

 

[Sektor]

You're welcome!

 

[pgc01]

Hi

Also:

Function LastSpace(s As String) As Long
 
LastSpace = InStrRev(Left(s, 30), " ")
 
End Function

Ex.:

=LastSpace(A1)

 

[anandvarma]

Try this

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 219px"> <col style="WIDTH: 64px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td> </td> <td>A</td> <td>B</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td>Hello SekTor, How are you doing?</td> <td style="TEXT-ALIGN: right">26</td></tr></tbody></table>
<table style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B1</td> <td>=FIND("/",SUBSTITUTE(A1," ","/",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))</td></tr></tbody></table></td></tr></tbody></table>