Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: How do I identify a period in a string of characters in a cell AND ensure that Mr. is before Mrs.

  1. #1
    New Member
    Join Date
    Nov 2013
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How do I identify a period in a string of characters in a cell AND ensure that Mr. is before Mrs.

    I am creating a mailing list and I want to ensure that the cell reads as follows "Mr. and Mrs. Excel". I need to ensure that every cell has a period after Mr and Mrs AND that they are in that order....Mr. first and then Mrs. I tried the find function but it doesn't automatically make the correction. Any other ideas would be very helpful. Thank you.

  2. #2
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,134
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: How do I identify a period in a string of characters in a cell AND ensure that Mr. is before Mrs.

    Excelnf,

    Welcome to the MrExcel forum.

    What version of Excel and Windows are you using?


    Can we see more examples of the strings?

    Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

    To post your data, you can download and install one of the following two programs:
    Excel Jeanie
    MrExcel HTML Maker

    Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
    See reply #2 the BLUE text in the following link:
    http://www.mrexcel.com/forum/about-b...ml#post2198045
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  3. #3
    New Member
    Join Date
    Nov 2013
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do I identify a period in a string of characters in a cell AND ensure that Mr. is before Mrs.

    Thank you Hiker95

    I am using Windows 7 and Excel 2010.

    This would be an example of the data. Except its about 4000 rows!

    Mr. and Mrs. Hiker
    Mr and Mrs Santee
    Mr. and Mrs Paper
    Mr and Mrs. Hall

    I need to make sure that all of them have a period after the word "Mr" and after the "Mrs". In addition, "Mr." always needs to be first in the cell.

    I was exploring the Search function. But I don't know how to make it check for BOTH periods. The one after Mr AND the one after Mrs?

    =SEARCH(".",A3, SEARCH("d",A3))

    The limitation with the Search function is that it only tells me if it has it or not but it doesn't replace it...at least I don't know how to get it to add the period at this point. The second limitation I have right now is that I don't know how to get it to check for both periods. I am so thankful you have replied to my post!

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,134
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: How do I identify a period in a string of characters in a cell AND ensure that Mr. is before Mrs.

    Excelnf,

    Sample raw data:

    Excel 2007
    A
    1Mr. and Mrs. Excel
    2Mr and Mrs. Excel
    3Mr and Mrs Excel
    4Mrs. and Mr. Excel
    5Mrs and Mr. Excel
    6Mrs and Mr Excel
    7Mr. and Mrs. Hiker
    8Mr and Mrs Santee
    9Mr. and Mrs Paper
    10Mr and Mrs. Hall
    11Mr & Mrs Hall
    12Mrs & Mr Hall
    13

    Sheet1





    After the macro:

    Excel 2007
    A
    1Mr. and Mrs. Excel
    2Mr. and Mrs. Excel
    3Mr. and Mrs. Excel
    4Mrs. and Mr. Excel
    5Mrs. and Mr. Excel
    6Mrs. and Mr. Excel
    7Mr. and Mrs. Hiker
    8Mr. and Mrs. Santee
    9Mr. and Mrs. Paper
    10Mr. and Mrs. Hall
    11Mr. & Mrs. Hall
    12Mrs. & Mr. Hall
    13

    Sheet1





    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    1. Copy the below code
    2. Open your NEW workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

    Code:
    Option Explicit
    Sub FixMrMrs()
    ' hiker95, 11/07/2013
    ' http://www.mrexcel.com/forum/easy-xl-add/737870-how-do-i-identify-period-string-characters-cell-ensure-mr-before-mrs.html
    Dim a As Variant, s, h As String, hs As String
    Dim i As Long, ii As Long, fmr As Long, fmrs As Long
    Dim mr As String, mrs As String
    a = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    For i = 1 To UBound(a, 1)
      h = "": hs = ""
      fmrs = Application.Find("Mrs", a(i, 1), 1)
      If fmrs = 1 Then
       hs = Right(a(i, 1), Len(a(i, 1)) - 1)
       fmr = InStr(hs, "Mr") + 1
      Else
        fmr = InStr(a(i, 1), "Mr")
      End If
      If fmr < fmrs Then
        s = Split(a(i, 1), " ")
        For ii = LBound(s) To UBound(s)
          If Left(s(ii), 2) = "Mr" Then
            If Right(s(ii), 1) <> "." Then
              h = h & s(ii) & ". "
            Else
              h = h & s(ii) & " "
            End If
          ElseIf Left(s(ii), 3) = "Mrs" Then
            If Right(s(ii), 1) <> "." Then
              h = h & s(ii) & ". "
            Else
              h = h & s(ii) & " "
            End If
          Else
            h = h & s(ii) & " "
          End If
        Next ii
        If Right(h, 1) = " " Then
          h = Left(h, Len(h) - 1)
        End If
        a(i, 1) = h
      ElseIf fmrs < fmr Then
        s = Split(a(i, 1), " ")
        For ii = LBound(s) To UBound(s)
          If Left(s(ii), 3) = "Mrs" Then
            If Right(s(ii), 1) <> "." Then
              h = h & s(ii) & ". "
            Else
              h = h & s(ii) & " "
            End If
          ElseIf Left(s(ii), 2) = "Mr" Then
            If Right(s(ii), 1) <> "." Then
              h = h & s(ii) & ". "
            Else
              h = h & s(ii) & " "
            End If
          Else
            h = h & s(ii) & " "
          End If
        Next ii
        If Right(h, 1) = " " Then
          h = Left(h, Len(h) - 1)
        End If
        a(i, 1) = h
      End If
    Next i
    Range("A1").Resize(UBound(a, 1), UBound(a, 2)) = a
    End Sub
    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

    Then run the FixMrMrs macro.
    Last edited by hiker95; Nov 7th, 2013 at 07:12 PM.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  5. #5
    New Member
    Join Date
    Nov 2013
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do I identify a period in a string of characters in a cell AND ensure that Mr. is before Mrs.

    Wow hiker95... you are awesome! I will definitely try this out! I have not used Macros so thank you for the instructions!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com