Spot the Blanks

Cyber_Cyrus

New Member
Joined
Jun 11, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.

1623424177336.png


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.

1623424396255.png


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:

1623424569899.png
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
ABCDEFGH
1FirstMiddleLastMobileStreetCityStateZip
2 MariannaScoutAston 254-259-38449239 Sage Dr.GarlandTX75043
3AnnisLettieHarris314-413-49607056 Spruce St. NorwalkCT 68510
4DaphneMarcelyn Bentley317-484-14259916 Paris Hill St.ValricoFL33594
5Shaw MadisonHopson412-372-28197934 Plumb Branch Ave. EasleySC 29640
6CaydenMikaylaFulton626-914-735060 River DriveVilla ParkIL60181
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:H6Expression=TRIM(A1)<>A1textNO
 
Last edited:
Upvote 0
Solution
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top