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]

Do you mean you want to convert this text to the abbreviated text using formulas?

 

[AndyD]

hi, wow, thanks for quick reply...

Yes i think so...so if is

London South West, it abreviates 2nd and 3rd word so reads London SW

and if is...
London South, abbreviates to London S etc, thanks
Andy

 

[Joe4]

Can't you just use the REPLACE functionality found under the EDIT drop down menu, i.e.

FIND WHAT: London South East
REPLACE WITH: London SE

If this does not do what you are looking for, please explain more fully what you would like to happen.

 

[AndyD]

I kinda need a formula or a bit of VBA

because

1) I need to use names in a big list in another application which can only accept a maximum of 15 characters

2) If the application is not presented with 15 characters it abbreviates for you...as you can see from below this means abbreviates some of the same....

ORIGINAL ABBREVIATED TO
Birmingham North Birmingham Nort
Birmingham North (0) Birmingham Nort
Birmingham North Res' Birmingham Nort
Birmingham South East Birmingham Sout
Birmingham South East (0) Birmingham Sout
Birmingham South East Res' Birmingham Sout
Birmingham South West Birmingham Sout
Birmingham South West (0) Birmingham Sout
Birmingham South West Res Birmingham Sout


i want to be able to abbreviate as, something like...

Birmingham North Birmingham N
Birmingham North (0) Birmingham N (0)
Birmingham North Res' Birmingham N Res
Birmingham South East Birmingham SE
Birmingham South East (0) Birmingham SE (0)
Birmingham South East Res' Birmingham SE Res
Birmingham South West Birmingham SW
Birmingham South West (0) Birmingham SW (0)
Birmingham South West Res Birmingham W Res
etc

any ideas appreciated (would rather not have a list for these to look-up as the list is queried from a database and can therefore change once new records added)

cheers
Andy

 

[al_b_cnu]

Hi,

How about this UDF:

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 & " " & sTemp
Exit Function
End If
Abbreviate = sResult & " " & Mid$(sTemp, I + 1, 1)

End Function

HTH

Alan

 

[Joe4]

al,

I am having problems with your function. It converts "London South" to "London S South"

 

[TommyGun]

I know there is a way to do this without using a UDF, but I can't remember it now. However here is a very ugly/simple UDF to what you want...

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> Abbreviate(value <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> tmp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

    tmp = <SPAN style="color:#00007F">CStr</SPAN>(value)

    tmp = Application.WorksheetFunction.Substitute(tmp, "South East", "SE")
    tmp = Application.WorksheetFunction.Substitute(tmp, "South West", "SW")
    tmp = Application.WorksheetFunction.Substitute(tmp, "North East", "NE")
    tmp = Application.WorksheetFunction.Substitute(tmp, "North West", "NW")
    tmp = Application.WorksheetFunction.Substitute(tmp, "North", "N")
    tmp = Application.WorksheetFunction.Substitute(tmp, "South", "S")

    Abbreviate = tmp
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

 

[AndyD]

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

 

[just_jon]

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")