Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-14-2019, 08:54 AM
0rion 0rion is offline Windows 10 Office 2010
Novice
 
Join Date: Jan 2019
Posts: 6
0rion is on a distinguished road
Default Adjust Acronym Script to include Dashes?

Hi,



I have data that contains dashes that I would like to abbreviate by just having the first letter of each word. I found several VBA Acronym scripts that work, but they look at the spaces so the words after the dashes get lost.

example: Projects-North America-Canada-Alberta-Moose Jaw

would yield: PAJ

I found a script that works much better:
Code:
Function Acronym(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(\w).*?(\W+|\s+|$)"
Acronym = UCase(re.Replace(str, "$1"))
End Function
Yields: PNACAMJ

Is there a way to modify this script to retain the dashes?

Desired: P-NA-C-A-MJ

Thanks.
Reply With Quote
  #2  
Old 01-14-2019, 10:39 AM
Kenneth Hobson Kenneth Hobson is offline Windows 10 Office 2016
Novice
 
Join Date: Jun 2018
Posts: 26
Kenneth Hobson is on a distinguished road
Default

Welcome to the forum!

I don't understand that first part.

You can make these two functions into one if you like.
Code:
Sub Test()
  MsgBox abbrev("Projects-North America-Canada-Alberta-Moose Jaw")
End Sub

Function abbrev(aString$) As String
  Dim a, b, i As Integer
  a = Split(aString, "-")
  b = a
  For i = 0 To UBound(a)
    b(i) = Acronym(CStr(a(i)))
  Next i
  abbrev = Join(b, "-")
End Function

Function Acronym(str As String) As String
  Dim re As Object
  Set re = CreateObject("vbscript.regexp")
  re.Global = True
  re.Pattern = "(\w).*?(\W+|\s+|$)"
  Acronym = UCase(re.Replace(str, "$1"))
End Function
Reply With Quote
  #3  
Old 01-14-2019, 11:38 AM
0rion 0rion is offline Windows 10 Office 2010
Novice
 
Join Date: Jan 2019
Posts: 6
0rion is on a distinguished road
Default

The first part was just to say that most 'acronym-making' scripts I found don't work with hyphenated words. The script I showed does. I'd just like to include the hyphens in the end result.

Actually, this code of yours works well:
Code:
Function abbrev(aString$) As String
  Dim a, b, i As Integer
  a = Split(aString, "-")
  b = a
  For i = 0 To UBound(a)
    b(i) = Acronym(CStr(a(i)))
  Next i
  abbrev = Join(b, "-")
End Function
although I notice that if there is a space after the hyphen it will show up in the acronym. Can that be fixed?
Reply With Quote
  #4  
Old 01-14-2019, 04:40 PM
Kenneth Hobson Kenneth Hobson is offline Windows 10 Office 2016
Novice
 
Join Date: Jun 2018
Posts: 26
Kenneth Hobson is on a distinguished road
Default

Code:
b(i) = Acronym(Trim(CStr(a(i))))
Reply With Quote
  #5  
Old 01-15-2019, 07:48 AM
0rion 0rion is offline Windows 10 Office 2010
Novice
 
Join Date: Jan 2019
Posts: 6
0rion is on a distinguished road
Default

Brilliant. Thank you!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Acronym Finder Macro for Microsoft Word mars1886 Word VBA 13 05-10-2018 07:05 PM
Adding List Number to Acronym Table SerenityNetworks Word VBA 6 12-13-2016 01:36 PM
Acronym Macro to include surrounding text SerenityNetworks Word VBA 3 03-11-2016 05:43 PM
Acronym Finder Cray_Z Word VBA 14 09-22-2014 11:42 PM
Auto correct for Acronym mam9 Word 3 11-19-2012 01:35 AM


All times are GMT -7. The time now is 10:14 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft