How to replace field based on string length?

[rbrown088]

In my current workflow I have used text to columns to separate data that has been converted from a PDF format. However, due to spacing issues, text to columns does not yield the same date needed in a consistent singular column but the dates do land in 3 consecutive columns. Each date is 8 characters long (xx/xx/xx). 

I want to consolidate the dates into one column.

See example below, some dates do fall into [txt6] also, but all relevant dates are contained in either of the 3 columns.

Is it possible to write an IF function that replaces fields that do not have 8 characters with a value that could then be sorted? With the eventual goal of consolidating the 3 columns into one column with the desired dates?

[fmvizcaino · Accepted solution]

Hi @rbrown088 ,

You could use a formula tool with a length function to count the number of characters, something like this:

If length(txt5)= 8 then txt5

Elseif length(txt6)=8 then txt6

Else txt7

Endif

Let me know if that works for you.

Best,

Fernando Vizcaino

[rbrown088]

Thank you! @fmvizcaino

After running this formula, I realized my data had dates extending to txt15. In addition, txt5 would sometimes have a string that was 8 characters long but was not a date. See solution below.

if length(txt6)=8 then txt6

elseif length(txt7)=8 then txt7

elseif length(txt8)=8 then txt8

elseif length(txt9)=8 then txt9

elseif length(txt10)=8 then txt10

elseif length(txt11)=8 then txt11

elseif length(txt12)=8 then txt12

elseif length(txt13)=8 then txt13

elseif length(txt14)=8 then txt14

else txt5
endif