String functions (1 Viewer)

CosmaL · Nov 26, 2019

Hello everybody!

Could you please assist me?

I've got a field which contains 1 or 2 words.

I would like to get the following:
- If there's 1 word, the first 3 letters (its already ok with Left function)
- if there are 2 words, the first 2 letters from the first word and the 1st from the second (ex. Costas Kostas -> CoK). :banghead:

Any ideas?

Thank you in advance!

Regards,
Costas

MajP · Nov 26, 2019

The instr function will return position of space. 0 for no space. So build a UDF to get space position. Then need if then to build string

MajP · Nov 26, 2019

Once you get space position, you need the mid function. Can also use the split function to do this.

moke123 · Nov 26, 2019

You could try

Code:

Function SplitIt(strIN As String) As String
    Dim x As Variant
    Dim strOut As String

    x = Split(strIN, " ")

    Select Case UBound(x)

    Case 0

        strOut = Left(x(0), 3)

    Case 1

        strOut = Left(x(0), 2) & Left(x(1), 1)

    Case Else

        strOut = "?"

    End Select

    SplitIt = strOut

End Function

vba_php · Nov 26, 2019

or, if you want the simplest way:

Code:

Function getStr(strIN as string)

Dim spacePos As Integer

    spacePos = InStr(1, strIN, " ")
    
        If spacePos = 0 Then
            getStr = Left(strIN, 3)
        Else
            getStr = Left(strIN, 2) & Mid(strIN, spacePos + 1, 1)
        End If

End Function

Micron · Nov 26, 2019

vba_php said:

or, if you want the simplest way:

Code:

Function getStr(strIN)

Dim spacePos As Integer

    spacePos = InStr(1, strIN, " ")
    
        If spacePos = 0 Then
            getStr = Left(strIN, 3)
        Else
            getStr = Left(strIN, 2) & Mid(strIN, spacePos + 1, 1)
        End If

End Function

Your function doesn't return anything. Or does it, without having As String?

vba_php · Nov 26, 2019

Micron said:
Your function doesn't return anything. Or does it, without having As String?

whops! I corrected it. sorry bout that!

Micron · Nov 26, 2019

Preamble then I reveal what seems very ODD:

I think I'd have backup from many people here who would make (or at least have read) comments like "Your function doesn't return anything so why not just make it a sub?" You might see that where a function has no return data type specified. I get their drift, but IMHO that isn't necessarily bad form. Sometimes it's even necessary, like when calling a procedure as a control event property.

Out of curiosity I created a sub and called your function as

Code:

Dim str As String
str = getStr("dog cat")
MsgBox str

I got "doc" for a message.

WHAAAT?

MajP · Nov 26, 2019

Your function doesn't return anything. Or does it, without having As String?

The return type is optional. If nothing is declared it returns a variant.
It would return nothing if you did this

Code:

Function getStr(strIN)
   Dim spacePos As Integer
   dim rtn as string
   spacePos = InStr(1, strIN, " ")
        If spacePos = 0 Then
            rtn = Left(strIN, 3)
        Else
            rtn = Left(strIN, 2) & Mid(strIN, spacePos + 1, 1)
        End If
End Function

In order to return something you need to set the value to the function's name

variants are optional in lots of places

dim x
is the same as
dim x as variant

Code:

public function someFunction (x,y,z)
end function

same as

Code:

public function someFunction (x as variant,y as variant,z as variant) as variant
end function

so yes it does return something.

I got "doc" for a message.

pretty sure that is a typo and they got Dog as expected.

Micron · Nov 26, 2019

Well, I knew all of that except that a function returns a variant if not declared - even if you assign it a value. I guess that's a result of my paradigm because I wouldn't fail to declare a variable even if I wanted it to be a Variant. It's just bad form IMHO.

And no, if getting the 1st 2 from the 1st word and the 1st from the second word, if you pass "dog cat" it is "doc".

?getstr("dog cat")
doc

Thanks for the edification though - as always.

MajP · Nov 27, 2019

?getstr("dog cat")
doc

You are right. That is the correct answer as would be expected.

CosmaL · Nov 28, 2019

Thank you all for the help!

I've used split function and it's working perfectly!

String functions (1 Viewer)

CosmaL

Registered User.

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

moke123

AWF VIP

vba_php

Forum Troll

Micron

AWF VIP

vba_php

Forum Troll

Micron

AWF VIP

MajP

You've got your good things, and you've got mine.

Micron

AWF VIP

MajP

You've got your good things, and you've got mine.

CosmaL

Registered User.

Similar threads

Users who are viewing this thread