It is almost always a better idea to clean up and parse your data
before importing it into a new system (here QuickBase). I am going to assume you have already looked at this option and are accepting the fact that the un-parsed data has landed in your application.
You used the word "consistency" in describing the information you want to parse out of the text field. More than likely your data is a lot less consistent than you assume as information could appear out of order, could be missing, or contain unexpected characters. One example of the data is insufficient to derive its actual format and structure. Because of this variability is probably best to initially manually supervise the parsing of the data rather than completely automate the process.
Here is a quick demo -
Click the Parse button to parse the fields and click the
Reset button to reset the fields:
Regexp Capturing Groupshttps://haversineconsulting.quickbase.com/db/bnm9bcsys?a=er&rid=1Pastie Databsasehttps://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=652Notes:(1) To simplify the maintenance of the demo I have made the
[Text] field
readonly on the HTML form and provided a
Reset button to clear the parsed fields.
(2) For the
City,
State and
Zip information I have parsed the string into individual fields.
(3) This is just a simple demo of parsing data out of a field using
script and
regular expressions. In fact I wrote the script to allow a user unfamiliar with
regular expressions to modify the script without having to understand every detail. However,
regular expressions are so powerful that it could easily be adopted for much more complex parsing requirements.
If you need further assistance with implementing this solution feel free to contact me off-world using the information in my profile:
https://getsatisfaction.com/people/dandiebolt/
