Extract text efter a certain character VBA

[haseft]

hi!
In ActiveCelli I have text "Summer_0,7"
I want to get all nummer efter character "_".
The following do not work, please help.
Sub FindThenummer()
Dim nm As Long
nm = Right(ActiveCell, InStr(ActiveCell, "_"))
ActiveCell.Offset(0, 1).Value = nm
End Sub

 

[Rick Rothstein]

Does this do what you want...

Sub FindThenummer()
  Dim nm As Double
  nm = Split(Replace(ActiveCell, ",", "."), "_")(1)
  ActiveCell.Offset(0, 1).Value = nm
End Sub

 

[haseft]

no, Rich
it return 7
I am looking för 0,7 (a nummer)
i use ","for decimal insted for "." in Excel

 

[Rick Rothstein]

no, Rich
it return 7
I am looking för 0,7

The comma is not my decimal point, so I wasn't sure how your system needed to handle it. Alright, try it this way instead...

Sub FindThenummer()
  Dim nm As Double
  nm = Split(ActiveCell, "_")(1)
  ActiveCell.Offset(0, 1).Value = nm
End Sub

 

[haseft]

Tanks Rick!
its working nu.
I wonder what (1) means.
Nu I want to get all text befor character "_" in ActiveCell.Offset(0, 2).Value = nm

 

[Rick Rothstein]

I wonder what (1) means.
Nu I want to get all text befor character "_" in ActiveCell.Offset(0, 2).Value = nm

The Split function creates a zero-based array... the (1) is the second element of that array. To get the value before the underbar, replace (1) with (0).

 

[haseft]

Tank Rick!

 

[rlv01]

Another way

 Sub FindThenummer()    
    Dim SPos As Long
    Dim Before As Variant, After As Variant

    With ActiveCell
        SPos = InStr(.Value, "_")
        If SPos > 0 Then
            Before = Left(.Value, SPos - 1)
            After = Mid(.Value, SPos + 1, Len(.Value))
        Else    ' "_" not found
            After = 0    'or whatever you want
            Before = .Value    'or whatever you want
        End If
        .Offset(0, 1).Value = After
        .Offset(0, 2).Value = Before
    End With
End Sub