Spot the Blanks

[Cyber_Cyrus]

Good Day All,

I have been assigned the task to identify all fields relating to our customer object within the database which has a leading or trailing space within. The fields include First Name, Last Name, Mobile etc. I managed to pull the data from the database into an Excel Spreadsheet using a query. I copied the data, and pasted in into another sheet on the same workbook, let's call it sheet 2. On Sheet 2, I trimmed all the fields, and additionally used the LEN function to calculate the number of characters in each field and also show the SUM of all LEN Values.

With the exception of the TRIM function, I did exactly the same on sheet 1. I performed a Vlookup within sheet 2 to bring up the SUM of all LEN values in sheet 1, and compared the two to spot where the Value are false, which would mean that somewhere a "space" resided which should not be there.

My question is simply this, what is the easiest way to identify in which field the "extra" space resides without running a comparison for each and every field? Please see below my attempts to accomplish this:

 

[shknbk2]

Welcome to the forum.

If it is just a matter of visually identifying where they are, this conditional formatting could work on your original data:

Note: data grabbed from random generators on the internet (I restricted the names to English names because some of them were really out there when the selection was worldwide!). As it turns out, there is a Sage Dr. in Garland, TX but no 9239 that Google could find.

The highlighted cells were random ones I selected to put spaces in front or behind to test it out

test.xlsx
	A	B	C	D	E	F	G	H
1	First	Middle	Last	Mobile	Street	City	State	Zip
2	Marianna	Scout	Aston	254-259-3844	9239 Sage Dr.	Garland	TX	75043
3	Annis	Lettie	Harris	314-413-4960	7056 Spruce St.	Norwalk	CT	68510
4	Daphne	Marcelyn	Bentley	317-484-1425	9916 Paris Hill St.	Valrico	FL	33594
5	Shaw	Madison	Hopson	412-372-2819	7934 Plumb Branch Ave.	Easley	SC	29640
6	Cayden	Mikayla	Fulton	626-914-7350	60 River Drive	Villa Park	IL	60181
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:H6	Expression	=TRIM(A1)<>A1	text	NO

 

[Cyber_Cyrus]

Hi there shknbk2,

Thank you for the welcome and your response.

Yes, I just need to identify them. Your solution will work 100%.

I still need to learn a lot about Excel, and the challenge thereof seems daunting. That being said, I know I can learn much from each and every one within this community. Thank you again for your time and assistance. I deeply appreciate it

 

[shknbk2]

You're welcome. Glad to help.