Solved Trim (?) function (1 Viewer)

[CosmaL]

Dear friends,

i've got some cells with various string data.
All of them contain-for sure- a sequence of characters like H500, H501, H600, H603 etc

Is there any way to exctract only this sequence (H***) from each cell?

Thank you in advance!

 

[arnelgp]

create a function in a Module:

Public Function fnH_Only(ByVal p As Variant) As Variant
Dim var, v
Dim ret As Variant
If IsNull(p) Then Exit Function
p = p & ""
With CreateObject("Vbscript.RegExp")
    .pattern = "\H\d{3}"
    .Global = True
    .ignorecase = True
    Set var = .Execute(p)
    For Each v In var
        ret = ret & v & ", "
    Next
    If Len(ret) Then
        ret = Left$(ret, Len(ret) - 2)
    End If
End With
fnH_Only = ret
End Function

example:

=fnH_Only(A1)

 

[cheekybuddha]

=IF(Left(A1, 1) = "H", A1, "")

Place that in a new column and then drag down

 

[ebs17]

Public Function IsolateExpression(ByVal AnyString As String) As String
    Static oRegEx As Object
    If oRegEx Is Nothing Then Set oRegEx = CreateObject("Vbscript.RegExp")
    With oRegEx
        .Pattern = ".*(H\d{3}).*"
        IsolateExpression = .Replace(AnyString, "$1")
    End With
End Function

' -------------------------------------------------------------------------------------
? IsolateExpression("  H456 piece")
H456

 

[arnelgp]

just tested:

?IsolateExpression("All of them contain-for sure- a sequence of characters like H1")

Result:
All of them contain-for sure- a sequence of characters like H1

 

[ebs17]

All of them contain-for sure- a sequence of characters like H500, H501, H600, H603 etc

I read the assignment and I think @CosmaL means it.
All, for sure... words have meaning (to me).

 

[cheekybuddha]

Why are you both messing about with VBA?

 

[arnelgp]

Why are you both messing about with VBA?

i've got some cells with various string data.

we don't know "where" in the string is the character sequence.

 

[cheekybuddha]

OK, but you can still do it using formulas (yes, much more complicated!) but no need to force macro-enabled workbook plus all the headaches that brings.

I misunderstood the requirement - due to language barrier it's not totally clear that OP is looking for cells containing Hnnn sequence only or strings within cells containing Hnnn sequence.

With your interpretation formula could be something like:

=IF(
  AND(
    SEARCH("*H???*", A1) > 0,
    ISNUMBER(
      MID(A1, SEARCH("*H???*", A1) + 1, 3)
    )
  ),
  MID(A1, SEARCH("*H???*", A1), 4),
  ""
)

(NB untested! )

 

[arnelgp]

(NB untested! )

tested it, nothing happens.

 

[cheekybuddha]

OK, opened excel!

This works better but will fail if there is another h before the Hnnn sequence:

=IF(
  ISERROR(
    IF(
      AND(
        SEARCH("H???", A1) > 0,
        ISNUMBER(
          MID(A1, SEARCH("H???", A1) + 1, 3) + 0
        )
      ),
      MID(A1, SEARCH("H???", A1), 4),
      ""
    )
  ),
  "",
  IF(
    AND(
      SEARCH("H???", A1) > 0,
      ISNUMBER(
        MID(A1, SEARCH("H???", A1) + 1, 3) + 0
      )
    ),
    MID(A1, SEARCH("H???", A1), 4),
    ""
  )
)

 

[CosmaL]

Thank you all!

I've used the proposed function from arnelgp !