Grab a character using LEFT while ignoring specific characters

Question

Ok, so I understand you can grab any character using the LEFT function:

Left(Right(A,3),1)

(big thanks to Serenity for showing me this)

What I'm trying to figure out is, if you have variables set like this:

myTxt1 = "2513,82 alpha"
myTxt2 = "999,71 somekindofexpression"
myTxt3 = "55,7 orange"

... how do you make them come out as:

myTxt1 = "2513,82"
myTxt2 = "999,71"
myTxt3 = "55,7"

After that I can easily grab the "," using the Left(Right(A,3),1) code mentioned above.

All of this doesn't have to be done using the LEFT function, but I'm assuming that would be the way to go, or maybe potentially regex could clear it all at once?

Thanks!

It's really not clear what you want the resulting string to be. You said ignore the word "alpha" and start from the number, but then you said the result would just be ",". — rory.ap, Feb 22 '15 at 22:55

Dan Donoghue · Accepted Answer · 2015-02-23T00:22:54.403

This will get everything to the left of the space:

Sub myTxt()
'Set the strings according to your post
myTxt1 = "2513,82 alpha"
myTxt2 = "999,71 somekindofexpression"
myTxt3 = "55,7 orange"
'Split the strings to an array using a space as the delimiter then assign the first element to the variable
myTxt1 = Split(myTxt1, " ")(0)
myTxt2 = Split(myTxt2, " ")(0)
myTxt3 = Split(myTxt3, " ")(0)
'Display the results
MsgBox "myTxt1 = " & myTxt1 & vbLf & "myTxt2 = " & myTxt2 & vbLf & "myTxt3 = " & myTxt3
End Sub

Change the 0 to a 1 to get the next set of data until another space is encountered. You can keep incrementing the number until it runs out of blocks of text. To find the maximum blocks use ubound(Split(myTxt1, " "))

If you have your heart set on using the left function, you can find the number char of the space using instr (In String):

instr(1,myTxt1," ")

You can then couple this with a left function like this:

Left(myText,instr(1,myTxt1," ")-1) 'Remove 1 to get rid of the space from the returned string

Lastly, you can use an array in here to allow for scalable amounts of input like so:

Sub myTxt2()
Dim myTxt As Variant, X As Long
'Input your data to an array (Comma seperate your values)
myTxt = Array("2513,82 alpha", "999,71 somekindofexpression", "55,7 orange")
'Loop through the array one element at a time
For X = LBound(myTxt) To UBound(myTxt)
    'Replace the element with just the first chunk of the value
    myTxt(X) = Split(myTxt(X), " ")(0)
Next
'Display results
MsgBox Join(myTxt, vbLf)
End Sub

Your data is still just as accessible, but instead of myTxt1, myTxt2, myTxt3, it would now be myTxt(1), myTxt(2), myTxt(3) respectively

Hope this helps you out now and in the future.

Thanks! This works perfectly, but I'm afraid I ran onto a whole different problem along the way... Looks like referring to space won't work in every situation, as sometimes I end up with other unnecessary characters. Can you come up with something that can simply clear EVERYTHING but the numbers, comma and a period? Or simply clear everything that's two spaces after the period or comma to the right. "number.##" or "number,##" is pretty much all I need to end up with in the end. Or should I just start a whole new question? — blackwind, Feb 23 '15 at 01:11
nvm, pulled it off by studying the code here: [link](http://stackoverflow.com/questions/25102372/how-to-use-enable-regexp-object-regular-expression-using-vba-macro-in-word) — blackwind, Feb 23 '15 at 01:43

Serenity · Answer 2 · 2015-02-23T00:05:11.063

Here's some code where I go looking for a number, eventually removing all non numeric (and non decimal and non comma) characters from string till I find a recognisable number.

    Lne = Inp.readline
    SortKey = Mid(Lne, LCase(Arg(3)), LCase(Arg(4)) - LCase(Arg(3)))
    If IsNumeric(Sortkey) = False then
        Set RE = new Regexp
        re.Pattern = "[^0-9\.,]"
        re.global = true
        re.ignorecase = true
        Sortkey = re.replace(Sortkey, "")
    End If
    If IsNumeric(Sortkey) = False then
        Sortkey = 0
    ElseIf Sortkey = "" then
        Sortkey = 0
    ElseIf IsNull(Sortkey) = true then
        Sortkey = 0
    End If
    .AddNew
    .Fields("SortKey").value = CSng(SortKey)
    .Fields("Txt").value = Lne
    .UpDate
Loop

It's from a bigger program.

It's reads a line of text from StdIn. It then extracts a colummn by chyaracter position Arg(3) etc are command line parameters 3 = start column, 4 = end column.

It tests if the columns extracted are a number. If not, it does a search and replace removing all characters not 0-9, a comma, or a decimal.

I then test if it's now a number, if not I set to 0, else I convert to a single datatype.

For you it's the search and replace, you are only left with numbers, decimal point and comma.

Thanks, but is there any way you could please explain and apply this on my example? It's a bit too much for me. Plus I'd really like to comprehend it, instead of just copy/paste. — blackwind, Feb 22 '15 at 23:20

score 0 · Answer 3 · answered Feb 23 '15 at 06:01

Here's the solution I used in the end. It includes RegExp function, so you're going to have to enable "Microsoft VBScript Regular Expressions 5.5" in Tools/References.

Dim myVal as Variant
Dim colMatches As MatchCollection
Dim objMatch As Match

myVal = "We've earned €20.000,00 last night."

Set rgex = New RegExp
    rgex.Pattern = "[0-9].*[0-9]"
Set colMatches = rgex.Execute(myValue)
For Each objMatch In colMatches
      myValue = objMatch.Value
MsgBox myValue 'shows "20.000,00"
Next

After that, I've traced the "," symbol with "Left" function

Dim decSym As String
decSym = Left(Right(myValue, 3), 1)
MsgBox decSym 'shows ","

Grab a character using LEFT while ignoring specific characters

3 Answers3