We are a group of users of Blackbaud products and are not affiliated with Blackbaud. We'd love to have you join our community to help and be helped in getting the most from your Blackbaud software.
Register now to join us to get independant advice on your system, connect with 3rd party consultants to help you maximize your database and have a real alternative to the official Blackbaud website.
what expression do I use to extract so I can get the text like I want?
I started with Left([field], 6) and that results in "Note - "
I want to parse out at each space, so that way I can allow for variations of the original text.
I'd be happy with:
Field 1: "Note -"
Field 2: "Addr1"
Field 3: "ID 8903"
Field 4: by
Field 5: Elaine Tucker
How do I do that?
Thanks,
Elaine
__________________ Elaine Tucker Stewardship Coordinator St. Mark's School of Texas USA www.smtexas.org
If you need to do this in Access, you're probably better off writing a User-Defined Function in VBA for Access. There are just too many variables to write a simple formula to produce the results that you want. It's much easier to do this in VB, because you can do loops to handle situations like
"Note-" vs. "Note -" vs. "Ntoe -"
or
"Drew Allen" vs. "D. Allen" vs. "J. Drew Allen"
If you're not tied to MS Access, it's actually very easy to do this in Excel. Just use the "Data >Text to Columns" and specify that "Space" is one of the delimiters. Then you have the joy of making sure that all of the similar items are in the same columns, so that you can write your formulas to produce the results you actually want.
Drew
__________________ J. Drew Allen
Children's Hospital of Philadelphia
Crystal Reports and SQL Server Consultant
It is better to live your destiny imperfectly than to live an imitation of somebody else's life with perfection.
If you need to do this in Access, you're probably better off writing a User-Defined Function in VBA for Access. There are just too many variables to write a simple formula to produce the results that you want. It's much easier to do this in VB, because you can do loops to handle situations like
"Note-" vs. "Note -" vs. "Ntoe -"
or
"Drew Allen" vs. "D. Allen" vs. "J. Drew Allen"
If you're not tied to MS Access, it's actually very easy to do this in Excel. Just use the "Data >Text to Columns" and specify that "Space" is one of the delimiters. Then you have the joy of making sure that all of the similar items are in the same columns, so that you can write your formulas to produce the results you actually want.
Drew
Drew,
Have I told you lately that YOU ROCK!!!!! OMG - the excell is so much easier.... I automatically go to access but your solution is EASY.... (I'm feeling a little duh! at the moment) LOL
We are in the beta testing phase of a bridge between RE and our web server.... I had been correcting this field one by one, until it dawned on me it would be faster to let it come in as is and then export/reimport.
Thanks a MILLION
__________________ Elaine Tucker Stewardship Coordinator St. Mark's School of Texas USA www.smtexas.org
In excel you can also use the MID function to get a certain number of characters from the middle of a field. You would have to concatenate it with a RIGHT function but that's not too hard. I've done it before with good success.
This one of the the "surprises" I had after I came on board - at first I was extremely skeptical, but after almost 1 year we've made some serious progress!
Previous to trying this out we were doing dual entry in both sytems '
Now we've cut our DE time by at LEAST 75%!!!!!
Alot of my time is still wrapped up in this project but it's the data mgmt side:
1. - making sure that the fields are mapping/updating correctly
2. documenting problems & offering possible solutions
3. requesting site specific things that our org needs.
1.
2.
3....
1
2.
3.
(you know the wonderful software cycle!...)
Best Regards,
Elaine
__________________ Elaine Tucker Stewardship Coordinator St. Mark's School of Texas USA www.smtexas.org
In excel you can also use the MID function to get a certain number of characters from the middle of a field. You would have to concatenate it with a RIGHT function but that's not too hard. I've done it before with good success.
I just wanted to mention that these same concatenation functions can also be used in Access....
The MID function would not work in this case because all of the text is in varying lengths, there was no uniformity to it, so breaking it up by the spaces worked best.
__________________ Elaine Tucker Stewardship Coordinator St. Mark's School of Texas USA www.smtexas.org
'
Linear Mode
