Search

openoffice calc multiple search and replace array substitute

openoffice calc multiple search and replace array substitute

Today I had a spreadsheet that had a field populated by letters to represent missing fields (no telephone number? put a “T” in the column etc).

I could have ended up with up to 21 letters to represent the missing fields.

I needed to have a better representation so I could fill in the missing fields.

To accomplish this I had to do it in stages but I could do it all in one line:

=CONCATENATE(
IF(SUBSTITUTE(Sheet2.F2,"A","title_________ ",1)=Sheet2.F2,"","title_________ "),
IF(SUBSTITUTE(Sheet2.F2,"B","firstname_________ ",1)=Sheet2.F2,"","firstname_________ "),
IF(SUBSTITUTE(Sheet2.F2,"D","surname_________ ",1)=Sheet2.F2,"","surname_________ "),
IF(SUBSTITUTE(Sheet2.F2,"G","address2_________ ",1)=Sheet2.F2,"","address2_________ "),
IF(SUBSTITUTE(Sheet2.F2,"H","address3_________ ",1)=Sheet2.F2,"","address3_________ "),
IF(SUBSTITUTE(Sheet2.F2,"J","town_________ ",1)=Sheet2.F2,"","town_________ "),
IF(SUBSTITUTE(Sheet2.F2,"K","county_________ ",1)=Sheet2.F2,"","county_________ "),
IF(SUBSTITUTE(Sheet2.F2,"L","postcode_________ ",1)=Sheet2.F2,"","postcode_________ "),
IF(SUBSTITUTE(Sheet2.F2,"M","sex_________ ",1)=Sheet2.F2,"","sex_________ "),
IF(SUBSTITUTE(Sheet2.F2,"O","dob_________ ",1)=Sheet2.F2,"","dob_________ "),
IF(SUBSTITUTE(Sheet2.F2,"P","telephone_________ ",1)=Sheet2.F2,"","telephone_________ "),
IF(SUBSTITUTE(Sheet2.F2,"Q","mobile_________ ",1)=Sheet2.F2,"","mobile_________ "),
IF(SUBSTITUTE(Sheet2.F2,"R","email_________ ",1)=Sheet2.F2,"","email_________ "),
IF(SUBSTITUTE(Sheet2.F2,"S","service_________ ",1)=Sheet2.F2,"","service_________ "),
IF(SUBSTITUTE(Sheet2.F2,"T","RBL#_________ ",1)=Sheet2.F2,"","RBL#_________ "),
IF(SUBSTITUTE(Sheet2.F2,"U","join date_________ ",1)=Sheet2.F2,"","joindate_________ ")
)

Basically I need to check if the letter exists in the column and if so print the matching text with a line to write the missing information in.

To paste it into openoffice calc you need it all in one line:

=CONCATENATE(IF(SUBSTITUTE(Sheet2.F2,"A","title_________ ",1)=Sheet2.F2,"","title_________ "),IF(SUBSTITUTE(Sheet2.F2,"B","firstname_________ ",1)=Sheet2.F2,"","firstname_________ "),IF(SUBSTITUTE(Sheet2.F2,"D","surname_________ ",1)=Sheet2.F2,"","surname_________ "),IF(SUBSTITUTE(Sheet2.F2,"G","address2_________ ",1)=Sheet2.F2,"","address2_________ "),IF(SUBSTITUTE(Sheet2.F2,"H","address3_________ ",1)=Sheet2.F2,"","address3_________ "),IF(SUBSTITUTE(Sheet2.F2,"J","town_________ ",1)=Sheet2.F2,"","town_________ "),IF(SUBSTITUTE(Sheet2.F2,"K","county_________ ",1)=Sheet2.F2,"","county_________ "),IF(SUBSTITUTE(Sheet2.F2,"L","postcode_________ ",1)=Sheet2.F2,"","postcode_________ "),IF(SUBSTITUTE(Sheet2.F2,"M","sex_________ ",1)=Sheet2.F2,"","sex_________ "),IF(SUBSTITUTE(Sheet2.F2,"O","dob_________ ",1)=Sheet2.F2,"","dob_________ "),IF(SUBSTITUTE(Sheet2.F2,"P","telephone_________ ",1)=Sheet2.F2,"","telephone_________ "),IF(SUBSTITUTE(Sheet2.F2,"Q","mobile_________ ",1)=Sheet2.F2,"","mobile_________ "),IF(SUBSTITUTE(Sheet2.F2,"R","email_________ ",1)=Sheet2.F2,"","email_________ "),IF(SUBSTITUTE(Sheet2.F2,"S","service_________ ",1)=Sheet2.F2,"","service_________ "),IF(SUBSTITUTE(Sheet2.F2,"T","RBL#_________ ",1)=Sheet2.F2,"","RBL#_________ "),IF(SUBSTITUTE(Sheet2.F2,"U","join date_________ ",1)=Sheet2.F2,"","joindate_________ "))
I hope this helps somebody with the same general problem.

Related posts

Leave a Comment