Find first non-space (special) character in cell

[siers]

Hi All,

I'm hoping to find a formula that can identify the position of the first non-space or special character in a cell.


For example, I have three cells with the following data:

Abcde efgh - ijklmn op qrs tuvwzyz

Abc dee (fgh ijklmn opqr stuv wzyz)

Abcd: eefgh ijkl mnop qrstuv wzyz


The desired formula would return the following for each cell:

12
9
5


Thanks,
Jeff

 

[Marcelo Branco]

Maybe a UDF (User Defined Function)

Alt+F11 ti open the VBEditor

Menu
Insert > Module

Paste code below in the right panel

Function GetSpChar(s As String)
    Dim i As Long
    
    For i = 1 To Len(s)
        If Mid(s, i, 1) Like "[! A-Za-z1-9]" Then
            GetSpChar = i
            Exit Function
        End If
    Next i
End Function

Excel

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Text​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Abcde efgh - ijklmn op qrs tuvwzyz​

[/td][td]

12​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Abc dee (fgh ijklmn opqr stuv wzyz)​

[/td][td]

9​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Abcd: eefgh ijkl mnop qrstuv wzyz​

[/td][td]

5​

[/td][/tr]
[/table]

Formula in B2 copied down
=GetSpChar(A2)

Hope this helps

M.

 

[Rick Rothstein]

Maybe a UDF (User Defined Function)

Alt+F11 ti open the VBEditor

Menu
Insert > Module

Paste code below in the right panel

Function GetSpChar(s As String)
    Dim i As Long
    
    For i = 1 To Len(s)
        If Mid(s, i, 1) Like "[! A-Za-z[B][COLOR="#FF0000"][SIZE=2]1[/SIZE][/COLOR][/B]-9]" Then
            GetSpChar = i
            Exit Function
        End If
    Next i
End Function

Typo alert... the highlighted 1 should be a 0

 

[siers]

Wow thanks guys! Such a quick response, works perfectly.

 

[Marcelo Branco]

Typo alert... the highlighted 1 should be a 0

Rick,

Ah, you're 100% right (as usual).
Thank you for pointing out!

M.

 

[Marcelo Branco]

Wow thanks guys! Such a quick response, works perfectly.

You're welcome.

M.

 

[lrobbo314]

Another way.

Function SPECIAL(s As String) As Variant
Dim RX As Object: Set RX = CreateObject("VBScript.RegExp")

With RX
    .Pattern = "[^\w\s]"
    Set matches = .Execute(s)
End With

SPECIAL = InStr(s, matches(0))

End Function