How do Abbreviate words in a string?

[AndyD]

How do Abbreviate words in a string?

for example i need to abbreviate

London South East as "London SE"

London South West as "London SW"

London South as "London S" etc

Thanks
A

 

[TommyGun]

sorry, i havent used UDF's in a while - can u remind me how to use, thanks

Insert the code into a standard module, and then use the function as normal on a spreadsheet.

Hiya, TG: w/o UDF is pug-ugly, like --

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,"South East","SE"),"South West","SW"),"North East","NE"),"North West","NW"),"North","N"),"East","E"),"South","S"),"West","W")

That is exactly the same as what my "pug-ugly" UDF does.

 

[al_b_cnu]

thanks all

i think al_b_cnu's is more along the right lines because a list would need to be updated when new records are added to our database

sorry, i havent used UDF's in a while - can u remind me how to use, thanks
A

Hi,
Sorry about the bug, try

Function Abbreviate(Name As String) As String
Dim I As Integer
Dim sResult As String
Dim sTemp As String

I = InStr(Name, " ")
If I < 1 Then
    Abbreviate = Name
    Exit Function
End If

sResult = Left$(Name, I + 1)
sTemp = Right$(Name, Len(Name) - I)
I = InStr(sTemp, " ")
If I < 1 Then
    Abbreviate = sResult
    Exit Function
End If
Abbreviate = sResult & " " & Mid$(sTemp, I + 1, 1)

End Function

to insert, Alt-F11, Insert Module & paste code in.

HTH

Alan

 

[AndyD]

thanks, once ive pasted the code in...how do i get to use it?
cheers
A

 

[TommyGun]

In a cell input...

=Abbreviate(A1)

 

[AndyD]

thanks, sorry did that...didna work, got #NAME? error....any ideas for me?

 

[al_b_cnu]

Hi,

A modified version which will cater for > 2 abbreviations & also cater for the special case of the string '(x)':

Function Abbreviate(Name As String) As String
Dim I As Integer
Dim sResult As String
Dim sTemp As String

I = InStr(Name, " ")
If I < 1 Then
    Abbreviate = Name
    Exit Function
End If

sResult = Left$(Name, I)
sTemp = Name
Do While I > 0
    sTemp = Right$(sTemp, Len(sTemp) - I)
    If Left$(sTemp, 1) = "(" Then
        If Mid$(sTemp & "***", 3, 1) = ")" Then
            sResult = sResult & " " & Left$(sTemp, 3)
        Else
            sResult = sResult & " " & Left$(sTemp, 1)
        End If
    Else
        sResult = sResult & " " & Left(sTemp, 1)
    End If
    I = InStr(sTemp, " ")
Loop
Abbreviate = sResult

End Function

Regards

Alan

 

[AndyD]

sorry please ignore my last post..have got it to work now, thanks
A

 

[just_jon]

sorry, i havent used UDF's in a while - can u remind me how to use, thanks

Insert the code into a standard module, and then use the function as normal on a spreadsheet.

Hiya, TG: w/o UDF is pug-ugly, like --

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,"South East","SE"),"South West","SW"),"North East","NE"),"North West","NW"),"North","N"),"East","E"),"South","S"),"West","W")

That is exactly the same as what my "pug-ugly" UDF does.

Yeah, TG, but the UDF looks better...

 

[TommyGun]

Yeah, TG, but the UDF looks better...

Yup, but Aladin's post here looks even better!