megera716
Board Regular
- Joined
- Jan 3, 2013
- Messages
- 146
- Office Version
- 365
- Platform
- Windows
Our dental insurance carrier's bill formatting is THE WORST and I jump through 37 hoops using LEFT, TEXTAFTER and text to columns to make it usable for reconciliation every month.
One thing I haven't been able to automate so I've been fixing manually is some employees have a letter after their name, which looks like a middle initial but it is not an initial, it's a reason code. But others don't have this letter code at all so I can't just lop off the last 2 characters of every cell. Here is a list of employee names, last name first, with every letter except their first and last initial replaced by me with an asterisk for purposes of this post.
The letter, if they have one, is "A", "R" or "T". There is a space but no punctuation between the last letter of their first name and the reason code. I would like to remove the space AND the letter so I don't have to run yet another function (TRIM) to clean this freaking thing up every month. The first employee name is in cell A2.
One thing I haven't been able to automate so I've been fixing manually is some employees have a letter after their name, which looks like a middle initial but it is not an initial, it's a reason code. But others don't have this letter code at all so I can't just lop off the last 2 characters of every cell. Here is a list of employee names, last name first, with every letter except their first and last initial replaced by me with an asterisk for purposes of this post.
The letter, if they have one, is "A", "R" or "T". There is a space but no punctuation between the last letter of their first name and the reason code. I would like to remove the space AND the letter so I don't have to run yet another function (TRIM) to clean this freaking thing up every month. The first employee name is in cell A2.
| C*****, K****** A |
| C***, C******* |
| C******, M*** A |
| M****, J*** A |
| M******, V****** M**** R |
| N***, W**** |
| N*****, M****** |
| B****, C****** T |
| B*******, M****** A |
| B****, K**** T |
