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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
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:
Solution

Cyber_Cyrus

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,231
Messages
5,768,938
Members
425,506
Latest member
AndreaWorkPlace

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
Top