Click here to reset your password. You MUST have a valid e-mail address so that you may receive the instructions to complete the reset.

If this is your first visit, be sure to check out the Board FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.

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.

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!

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

1

Mr. and Mrs. Excel

2

Mr and Mrs. Excel

3

Mr and Mrs Excel

4

Mrs. and Mr. Excel

5

Mrs and Mr. Excel

6

Mrs and Mr Excel

7

Mr. and Mrs. Hiker

8

Mr and Mrs Santee

9

Mr. and Mrs Paper

10

Mr and Mrs. Hall

11

Mr & Mrs Hall

12

Mrs & Mr Hall

13

Sheet1

After the macro:

Excel 2007

A

1

Mr. and Mrs. Excel

2

Mr. and Mrs. Excel

3

Mr. and Mrs. Excel

4

Mrs. and Mr. Excel

5

Mrs. and Mr. Excel

6

Mrs. and Mr. Excel

7

Mr. and Mrs. Hiker

8

Mr. and Mrs. Santee

9

Mr. and Mrs. Paper

10

Mr. and Mrs. Hall

11

Mr. & Mrs. Hall

12

Mrs. & 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.