You've been there... Trying to do a VLOOKUP and it just doesn't want to work. I've been in the habbit of using =TEXT(TRIM(A1),0) whenever I do a VLOOKUP using employee numbers, because in so many of our reports, sometimes these IDs are numbers, sometimes they're text, and sometimes their both. Just making the TEXT and TRIM functions a habit has saved me some headache.
Today, I ran into the situation where this still didn't resolve the issue. For whatever reason, this new system we have has been designed to add trailing characters to IDs. They looks like spaces, but they aren't, and TRIM doesn't clean them up. They're actually the CHAR(160) symbol.
Anyway, this formula is likely overkill, but sometimes overkill is just enough. This formula combines several different text cleanup functions:
Often - normally when I'm doing something related to email or letter generation - I need an easy way to capture the person's first name to open my communication. Normally I'd use Text-to-Columns, but if the data is poorly structured and there's a mix of FIRST LAST with LAST, FIRST that isn't always an option. I put this formula together to recognize how the name is formatted, and grab the first name accordingly. Hopefully it saves you some time.
=PROPER(IFERROR(IFERROR(LEFT(RIGHT(A1,LEN(A1)-FIND(", ",A1)-1),FIND(" ",RIGHT(A1,LEN(A1)-FIND(", ",A1)-1))-1),RIGHT(A1,LEN(A1)-FIND(", ",A1)-1)),LEFT(A1,FIND(" ",A1&" ")-1)))
Many of us already understand the fundamentals of conditional formatting - highlighting cells that meet certain criteria, but what if you wanted to draw even more attention to the entire row of data. Maybe you have a large spreadsheet and some alert field is at the far end of your data set, and you don't want to keep scrolling to see whether a condition is met. Sometimes, it's helpful to highlight the entire row with conditional formatting.
The trick here is to create your condition and set your anchor so it stays in the target column, but the 1 but without the anchor so the row can freely move; =$B1="Awesome", but then apply the condition to more than one column.
I occasionally run into the situation where after creating the condition, I have to go back into the Rule and reset the row to 1. This target sometimes jumps to the bottom of the workbook when you first create the rule.
This is probably easier to follow in a video, so enjoy!