Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts

    Extracting uppercase text from a string

    Hi

    I found the following code but I do not know how to edit the pattern to get what I need.
    The string could look like the following:
    D:\test1and2\BERLIN - TCNY-F-Paid-JM.xlsm
    D:\test1and2\BERLIN.xlsm
    My goal in both examples is to extract the uppercase text in the middle (BERLIN). This uppercase text in the middle will vary.
    Hope I am clear with my request.

    Jean

    HTML Code:
    Option Explicit
    Sub SplitUpperName()
        Dim re As Object, mc As Object
        Dim r As Range, c As Range
        Dim s As String
    Set r = Range("A1", Cells(Rows.Count, "A").End(xlUp))
    Set re = CreateObject("vbscript.regexp")
    With re
        .Global = True
        .ignorecase = False
        .MultiLine = True
        .Pattern = "^\s*([A-Z\W]+\b)\W+([\w\s]+)"
    End With
    For Each c In r
        s = c.Text
        If re.test(s) = True Then
            Set mc = re.Execute(s)
                c(1, 2) = mc(0).submatches(0)
                c(1, 3) = mc(0).submatches(1)
        Else
            Range(c(1, 2), c(1, 3)).ClearContents
        End If
    Next c
        
    Range(r(1, 2), r(1, 3)).EntireColumn.AutoFit
    End Sub

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Jean,

    Will the text to be extracted always be part of the file name? Will the text to be extracted always follow a "\". In one sample there is a hyphen and additional uppercase letters: D:\test1and2\BERLIN - TCNY-F-Paid-JM.xlsm. Are these to be extracted along with BERLIN? Please provide the rules for extraction.

    Maud

  3. #3
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Hi Maud

    The text to be extracted will always follow a "\". I do not want any other text that are in uppercase letters. The string where I want the uppercase text will always follow after the second "\".

    Jean

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Jean

    if your string was in cell [A1] you could use this formula:
    Code:
    =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),2))+1,FIND(" ",SUBSTITUTE(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),2))+1,99),".xlsm"," "))-1)
    zeddy
    Last edited by zeddy; 2015-09-10 at 14:27.

  5. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2015-09-10)

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Jean,

    The User Defined Function assumes that the Uppercase text is the first part of the file name containing one word and is separated from the rest of the file name by either a <space>, "-", or a "."

    It also assumes that the filename will be located in a root directory folder one level deep containing 2 "\"s (c:\rootfolder\filename.xxx)

    Code:
    Public Function UcaseTxt(rng As Range) As String
        s = Split(rng, "\")
        t = Split(s(2), " ")
        u = Split(t(0), ".")
        v = Split(u(0), "-")
        UcaseTxt = v(0)
    End Function
    If your strings are in column A starting on row 2 then in B2 enter the following formula and copy down:

    cell B2 =UcaseTxt(A2)

    ucase1.png

    HTH,
    Maud

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2015-09-10)

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    Elegant and effective.

    zeddy

  9. #7
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts

    Extracting text

    Quote Originally Posted by zeddy View Post
    Hi Jean

    if your string was in cell [A1] you could use this formula:
    Code:
    =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),2))+1,FIND(" ",SUBSTITUTE(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),2))+1,99),".xlsm"," "))-1)
    zeddy
    Hi Zeddy

    This works great until it gets to a town with two names--such as OLD SAYBROOK. Egads!

    Jean

  10. #8
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Hi Maud

    I must be doing something wrong. In cell B2, I entered =UcaseTxt(A2) and I get #NAME?

    Jean

  11. #9
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Hi Maud

    I finally figured out what I was doing wrong and I am getting the text to extract correctly except I have the same issue when the town has two names such as NEW HAVEN.

    Jean

  12. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Thanks Zeddy; nice job on your formula as well!

    The User Defined Function assumes that the Uppercase text is the first part of the file name containing one word and is....
    Jean,

    Because there are many variables, it is good to identify all the rules up front to the process of the extraction. Here is the code modified to deal with multiple word extractions. The code line to split by spaces was removed and a trim function was added to the extracted value.

    Code:
    Public Function UcaseTxt(rng As Range) As String
        s = Split(rng, "\")
        t = Split(s(2), ".")
        u = Split(t(0), "-")
        UcaseTxt = Trim(u(0))
    End Function
    ucase2.png

    HTH,
    Maud

  13. The Following User Says Thank You to Maudibe For This Useful Post:

    JeanM (2015-09-11)

  14. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Does it matter if the text is NOT in uppercase?

    e.g.
    D:\test1and2\Chalfont St. Giles - TCNY-F-Paid-JM.xlsm
    D:\test1and2\Newcastle-Upon-Tyne - TCNY-F-Paid-JM.xlsm

    zeddy

  15. The Following User Says Thank You to zeddy For This Useful Post:

    JeanM (2015-09-11)

  16. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Zeddy,

    Completely looked past that!

    Amended code:

    Code:
    Public Function UcaseTxt(rng As Range) As String
        s = Split(rng, "\")
        t = Split(s(2), ".")
        u = Split(t(0), "-")
        For I = 1 To Len(u(0))
            c = Asc(Mid(u(0), I, 1))
            Select Case c
                Case 32, 65 To 90
                    count = count + 1
                Case Else
                    GoTo continue
            End Select
        Next I
    continue:
        UcaseTxt = Trim(Mid(u(0), 1, count))
    End Function
    ucase3.png

  17. The Following User Says Thank You to Maudibe For This Useful Post:

    JeanM (2015-09-11)

  18. #13
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Hi Maud

    Thank you very much for all the help. I will try to be more thorough in the future.

    Jean

  19. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    NP Jean. Glad to help

    Maud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •