CODEWITHSUNDEEP
excelstringvba
user155754
user155754

Reputation: 93

Export only text from the string, VBA

I have strings and I want to export only text from a string (delimiter in the string is "_"). For example:

string: Prijem_LPLC_MO-234_333333

extract: Prijem_LPLC

My code:

Public Function komentar(text_string As String) As String
Dim text() As String
Dim pom As String
text() = Split(text_string, "_")

komentar = ""
For i = LBound(text) To UBound(text) - 1
 If Application.IsText(text(i)) Then
    komentar = komentar & "_" & text(i)
 End If
Next i

End Function

But it copies full string with "_" at beginning.

Upvotes: 0

Views: 652

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

You are close.

For eliminating any string with digits, you need to check each character. With your method of putting together the result string, there will always be an underscore at the start. (An alternative would have an underscore at the end). So after you're done, merely remove the first character.

Option Explicit
Public Function komentar(text_string As String) As String
Dim text() As String
Dim pom As String
Dim i As Long, j As Long

text() = Split(text_string, "_")

komentar = ""
For i = LBound(text) To UBound(text)

'Check each character
 For j = 1 To Len(text(i))
    If Mid(text(i), j, 1) Like "#" Then Exit For
 Next j

'j will pass this test if no characters are digits
 If j > Len(text(i)) Then
    komentar = komentar & "_" & text(i)
 End If
Next i

'Strip off the first "_"
komentar = Mid(komentar, 2)

End Function

You could also do this with regular expressions in VBA, but that's a very different solution.

Upvotes: 0

Loowood
Loowood

Reputation: 21

Your code is a bit weird. If we go through it,

At first you are splitting the string

text() = Split(text_string, "_")

So in the array text, for the string Prijem_LPLC_MO-234_333333, you will have :

  • Prijem
  • LPLC
  • MO-234
  • 333333

Then, for each objects in your array, you test if the object is text :

Application.IsText(text(i))

Which is the case of every object in your array.

Finally, you concatenate komentar with this kind of structure :

"_ + object you were testing"

In your case, it'll be something like this :

First iteration : komentar = _Prijem

Second iteration : komentar = _Prijem_LPLC

Third iteration : komentar = _Prijem_LPLC_MO-234

Fourth iteration : komentar = _Prijem_LPLC_MO-234_333333

Indeed, if you want only the text from this string, without the "_" element, the problem is from this line :

komentar = komentar & "_" & text(i)

where you're not only adding the text, but also the "_"

For your code to work, you just have to change this part. Consider trying this :

komentar = komentar & text(i)

Post Scriptum : Also, just an advice here, I think using LBound(text) is maybe too much, while you can use 0, since you want to go through all your array.

For i = 0 To UBound(text) - 1

Upvotes: 1

marissalianam
marissalianam

Reputation: 13

Instead of komentar = "" add komentar = text(0). Your code copies the first symbol, because in this line youkomentar = komentar & "_" & text(i) the first time komentar is empty. So the code returns " _Prijem".

Upvotes: 0

Related Questions