I do a lot of data migrations, moving data from one system to another. Usually as part of a migration from a legacy system to Dynamics 365 CRM. Oftentimes I’m confronted with gnarly data in Excel spreadsheets that lack consistent formatting. Say you have different users entering Doctor’s names into a database, and they only have a free form text field. The users were instructed to put the doctors’ credentials after their name. They will likely enter it in any of the following formats:
- Gregory House, MD
- Merideth Grey MD
- Hannibal Lecter, D.O.
- Temperance Brennan, FA, OD
- Emmet Brown, Ph. D.
Trying to parse this data down is easy if you’re super-skilled Excel expert. Myself, I don’t do this every day so I have to go look up these formulas. Well, I have to share how easy this is now. See below for my back-and-forth with Bing AI Chat to get the place with my data can be easily cleaned-up.
I need an Excel function that strips off the end part of a text field. It is formatted like "lastname, MD" and I want to remove the ", MD" part.
Sure, you can use the `SUBSTITUTE` function in Excel to achieve this. Here’s an example:
“`excel
=SUBSTITUTE(A1, “, MD”, “”)
“`
In this formula, `A1` is the cell that contains the text you want to modify. This formula will replace all occurrences of “, MD” in the text with an empty string, effectively removing it. If the “, MD” text is not found in the cell, the original text will be returned unchanged. Please replace `A1` with the actual cell reference in your worksheet.
Remember to drag the fill handle (the small square at the bottom-right of the cell) down to apply this formula to the entire column if needed.
Let me know if you need further assistance!
That almost worked except there are more variations than just ", MD" -- need to adjust formula to remove anything at the end of the string starting with the comma.
If you want to remove anything after the comma, including the comma, you can use the `LEFT` and `FIND` functions in Excel. Here’s an example:
“`excel
=LEFT(A1, FIND(“,”, A1) – 1)
“`
In this formula, `A1` is the cell that contains the text you want to modify. The `FIND` function locates the position of the comma in the text, and the `LEFT` function extracts the text to the left of that position. If the comma is not found in the cell, the formula will return an error. To handle cells without a comma, you can use the `IFERROR` function:
“`excel
=IFERROR(LEFT(A1, FIND(“,”, A1) – 1), A1)
“`
This formula will return the original text if no comma is found. Please replace `A1` with the actual cell reference in your worksheet.
Remember to drag the fill handle (the small square at the bottom-right of the cell) down to apply this formula to the entire column if needed.
Let me know if you need further assistance!
That worked great! But there are some records where they omitted the comma and just used a space. Can we remove any text after the initial string of continuous text that starts (or is separated by) a comma or a space?
Sure, you can use a combination of Excel functions to achieve this. Here’s an example:
“`excel
=IFERROR(LEFT(A1, MIN(FIND({“,”, ” “}, A1&”, “&” “)) – 1), A1)
“`
In this formula, `A1` is the cell that contains the text you want to modify. The `FIND` function is used with an array of characters (comma and space) to locate the position of the first occurrence of either character in the text. The `MIN` function is used to find the smallest position number, which corresponds to the first separator in the text. The `LEFT` function then extracts the text to the left of that position.
If neither a comma nor a space is found in the cell, the `FIND` function will return an error. The `IFERROR` function is used to handle this by returning the original text.
Please replace `A1` with the actual cell reference in your worksheet.
Remember to drag the fill handle (the small square at the bottom-right of the cell) down to apply this formula to the entire column if needed.
Let me know if you need further assistance!
No comment yet, add your voice below!