I have a mobile number which is a “number” of course… and therefore has a leading 0, this gets removed on conversion of my data… and i’de like to keep it… any ideas ?
Hey @Amy! Sorry to hear you’re running into this issue. When you say it gets removed on “conversion of [your] data,” where is this happening exactly?
A potential fix could be using the Text Merge step (see example screenshot below)! This would add a “0” before every number in the column number. Does that make sense? Depending on where/how this problem is happening, there might be a better solution, though, so let me know.
I traced it and Parabola has the zero in the data, when it sends it to me I open the file in Excel and the leading Zero vanishes of course… any ideas how i can hang onto that ? Then i cull 90% of the file and import the new row.
Hi @Amy,
There is an easy way to force Excel to preserve leading zeroes in phone numbers — or other numbers like postal area codes for that matter. You need to prepend an ’ (apostrophe) symbol at the beginning of each cell that contains such data.
Note: the apostrophe symbol will be invisible when the cell is viewed on its own, but it will stay there.
Here is how it looks while I type:
And here is how the apostrophe disappears once I press Enter:
Ivan
3 Likes
I see that would work in typing it into a field… but i want the fix to work auto otheriwse i might as well just type a zero in the database at the end…
when i get the CSV file i open it in Excel… and its open… i then would have to append the ’ to each field… so its actually alot more typing than just adding a 0 in the dbase.
But thanks anyway
@Amy Sorry, I thought the solution was obvious.
You can automate the operation the way @andrew showed: do a TEXT MERGE operation and prepend with an apostrophe. Then when you open the processed file in Excel, the leading zero should show up.
Ivan
1 Like
It does now that i understand. Sorry i’m not a programmer. The issue is the data is fully inconsistent, some dumb men inputting 021555… and some 6421555 with 64 as the country code.
I totally feel your pain, I am currently designing a Parabola flow that requires proper formatting of international phone numbers myself.
Isn’t it viable for you to create formatting conditions for the 4-5 most common types of incorrect entry using FIND/REPLACE operations and flag all the rest as ‘wrong’ so that they can be looked over by hand? It could reduce manual work by a factor of 10 or more.
Here’s what I am doing for instance:
I have a column called PHONE # and numbers from 4 different countries listed there. I’ve noticed the most common issues I have to replace by hand (e.g. missing country code) and I’ve prepared a FIND/REPLACE for each one. In my particular case I benefit from the fact that I know what data to expect and that there are small variations in the errors. Perhaps you could derive a similar list of common input mistakes and compensate?
The ‘professional’ way to do it is use a Regular Expression but it requires a really smart person to write it.
2 Likes
Hubspot doesnt allow selection from a list … i think i just need to write instructions for them and tell them to follow them… it would be easy if it did
Hey Amy, I want to make sure that you get an answer to this. When you are in Parabola, you could use a Text Merge step to create a new column that contains first, an apostrophe, and then a reference to the column containing the phone number.
That way, you end up with '0624... which will keep the 0 when opened in Excel.
1 Like