Excel VBA extract specific text from cell

Tomkat · Jan 31st 2020

I have a cell with a very long string...
(this is only the last part of it)

EVO ITEMS (item folders/!CB Items/CBend0.bmp

and I need to extract JUST the "CBend0" from it.
Since the string is a filepath, I basically just need everything AFTER the last "/" and BEFORE the ".bmp"
I can do it on the sheet itself using formulas, but I can't seem to be able to do it in VBA.
Can anyone please help me?

ChobaniDevourer · Jan 31st 2020

This is coming from another beginner so take with grain of salt.

Code

Sub Stringtrim()
Dim Text1 As String
Dim Text2 As String
Dim Text3 As String

Text1 = Range("a1").Value ' input the cell value of the cell you want to trim in place of a1

Text2 = Right(Text1, 10)
Text3 = Left(Text2, 6)

Worksheets(1).Range("B1").Value = Text3 'same with this, change b1 to the cell you want it to output trimmed value to

End Sub

Display More

Tomkat · Jan 31st 2020

Thanks, but that won't work, as the .bmp filename may not always be the same length.
So specifying the value of "10" won't always yield the desired result.

ChobaniDevourer · Jan 31st 2020

But if it always ends in CBend0.bmp thats where the 10 comes from. No problem tho, lets wait for a more experienced person:D

Tomkat · Jan 31st 2020

But it doesn't... that last part of the filepath is always different.

gijsmo · Feb 1st 2020

Maybe try something like:

Code

Function ExtractText(sInput As String) As String
Dim sArray() As String

sArray = Split(sInput, "/")
ExtractText = sArray(UBound(sArray))
sArray = Split(ExtractText, ".")
ExtractText = sArray(LBound(sArray))
End Function

Sub Test()
Dim sText As String

sText = ExtractText(Sheet1.Range("A1"))

Sheet1.Range("B1").Value = sText
End Sub

Display More

This assumes of course that the filename you are trying to extract before the ".bmp" extension does not contain any other full stops eg, CBend.0.bmp would only return the CBend value.

**Carim** · Feb 1st 2020

Hello,

Attached is your test file with your customized UDF

Code

Function tomkat(sInput As String) As String
  Dim sArray() As String
  sArray = Split(sInput, "/")
  tomkat = Replace(sArray(UBound(Split(sInput, "/"))), ".bmp", "")
End Function

Hope this will help

Tomkat · Feb 2nd 2020

Thanks for all the replies!

gijsmo's solution was exactly what I needed!

Excel VBA extract specific text from cell

Participate now!

Share