Filter Contacts with First Names containing Spaces

[michaeljay201]

How can I filter the First Name field to find all contacts in which the First Name includes a space? 

Example: John M. Taylor ... First Name contains a space between "John" and "M."

I have made several attempts at Filtering the First Name by Contains Exactly, none of which works:

1) Contains Exactly: space (literally, just hit th space bar once, then Enter), but this spits back an error code: 

Api[name=crm-search, httpMethod=POST, subPath=/crm-search/search/ids] Failed to process your request. Error code was '400'. Error message was: {"status":"error","message":"Unknown Contacts Search API failure: [400] {\"status\":\"error\",\"message\":\"Neither value or values was set on the filter, but exactly one of the fields is required. filter={\\\"operator\\\":\\\"EQ\\\",\\\"property\\\":\\\"firstname\\\",\\\"value\\\":\\\"\\\",\\\"values\\\":[],\\\"rollForward\\\":false,\\\"rollingOffset\\\":0}\",\"correlationId\":\"93304a8a-31e9-4e89-b7d1-2ef15a7b464d\"}","correlationId":"93304a8a-31e9-4e89-b7d1-2ef15a7b464d"}

2) Contains Exactly: * * (*+space+*) ... Two wildcard asterisks separated by a space, but this returns zero contacts, even though I know there are dozens of contacts among my 65,000+ that contain a space in the First Name.

3) Contains Exactly: *n M* (*+n+space+M+*) ... This method returns every contact with *n; it ignores the space and the M*. So, not only do I see John M. Smith, but also every single contact with an "n" in the First Name, all 11,000+ of them.

4) Contains Exactly:  M* (space+M+*) ... Same issue as #3, but all contacts with an "M" in the First Name that is followed by other characters; slightly fewer at 5,000+.

It is not the end of the world to keep the space in the First Name for these contacts, but leaving the spaces affects readability and formatting when sending emails containing personalization tokens. In the Example, the salutation of a marketing email to Mr. Smith would be "Hello, John A.." -- which is clunky and feels less personalized than "Hello, John."

So, I would like to find these contacts and move the Middle initials or Last Names (when that happens) to the appropriate property to keep the First Name the first name only. 

But, I cannot find a way to do this with filters. I would have to export my ENTIRE contact database, run a query/find in Excel, adjust the properties in Excel, then import the affected contacts back into Hubspot...

If that's the only way, so be it, but it would be nice to have the database to the hard work for me. 🙂

[Marcy_Chand]

It doesn't appear this is possibly using Operations Hub either. I tried to build a workflow using format data, you can cut characters but when you add to look for a space, it brings up everyone in the databse just like a list does. 

[ChaosFreak]

Thanks, but the "Ideas Forum" is where ideas go to die. Very few ever end up being implemented.

And, seriously, this feature is so basic it's mind-boggling that anyone would need to "suggest" it to you. It's like I bought a pair of shoes and the right shoe had no shoelaces, so they tell me to "suggest" that "idea" in a forum.

[CBN]

Well put @ChaosFreak - this is exactly the feeling I also have about the Ideas Forum and HubSpot in general. There are too many things that do not 'just work'.

[michaeljay201]

@karstenkoehler Thank you for the reply. I had the feeling a native solution would not present itself. Never hurts to ask, though!

I will look into a Zap setup in lieu of the manual process. 

[karstenkoehler · Accepted solution]

Hi @michaeljay201,

I think the HubSpot search is currently not well equipped enough to handle this type of search, unfortunately. That means that you'd have to look into other features, either the new data quality automation in Operations Hub Professional or Enterprise (see here) or integrations data quality integrations like Incycle which can potentially handle this. Alternatively, you could also look into a simple Zapier setup, syncing contacts into a Google Sheet, performing the search and clean-up there and syncing the contacts back.

... or run an export once a month / before each bigger email campaign.

Hope this helps!

[ChaosFreak]

How in any universe is this considered an "accepted answer" and that this issue is "solved". This issue is not "solved". "HubSpot is unable to do this" is not a solution.

Why does HubSpot lack such basic features? I can search for first names containing any character EXCEPT for the space character? Why does it fail so badly? Any database can search a field for a specific character. Why not HubSpot?

[kvlschaefer]

Hi @ChaosFreak,

Thank you for your feedback!

I would like to apologize for the frustrations, data issues, and business impact this has caused.

I would recommend sharing your product feedback at our ideas forum.

This is the best place to give feedback to the Product Team as they regularly monitor the space. 

Thank you!

Best,

Kristen

[CBN]

Please refer to this idea @kvlschaefer 

https://community.hubspot.com/t5/HubSpot-Ideas/Filter-Contacts-with-First-Names-containing-Spaces/id...