Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 26, 2020 11:35 AM
Hi,
I am trying to clean some of my data but I’m not sure which formula to use.
I have three columns (URL, Date, Data).
I need to extract the strings from the data column, the date and the URL (which are always different).
In the Date column, I have LEFT(Data,10)
which returns the date perfectly.
In my URL column, I have MID(Data,11,256)
which returns the URL. I’ve set up a count of 256 characters to be sure I get all of it but I’m sure there is a more elegant way to do it.
Now the main problem is that sometimes I get 2 identical URL in the data column but I only need one of them.
So how can I be sure to always get the first URL and discard the second one if I get a duplicate one.
Many thanks in advance!
Solved! Go to Solution.
Oct 26, 2020 01:12 PM
Thank you @kuovonne! It is working perfectly now :slightly_smiling_face:
I forgot to mention it was not a space between the URLs but a new line so I modified your formula slightly. Also I wrapped it in a trim function because I was having a empty line before the URL.
TRIM(
MID(
{Data},
11,
IF(
FIND("\n", {Data}, 12),
FIND("\n", {Data}, 12)-11,
LEN({Data})
)
)
)
Oct 26, 2020 11:58 AM
Do you know if there will always be a space after the first url if there are multiple urls? Can you always discard the seond url?
MID(
{Data},
11,
IF(
FIND(" ", {Data}, 12),
FIND(" ", {Data}, 12)-11,
LEN({Data})
)
)
Oct 26, 2020 01:12 PM
Thank you @kuovonne! It is working perfectly now :slightly_smiling_face:
I forgot to mention it was not a space between the URLs but a new line so I modified your formula slightly. Also I wrapped it in a trim function because I was having a empty line before the URL.
TRIM(
MID(
{Data},
11,
IF(
FIND("\n", {Data}, 12),
FIND("\n", {Data}, 12)-11,
LEN({Data})
)
)
)