#value! errors in data lists

Originally Posted by nikond80

Howdy All,

I have a list of data that needs to be split into various fields and then put into a new script to be run on a database. The data always arrives in the same format. BUT not always fully populated. i.e :

address

add1, add2, add3,.
add1, add2, add3,add4,. Postcode
add1, add2,. Postcode

the only constant is ',' between address lines and a '.' before the postcode. I have managed to set up the following commands to split some data -

length of address - = LEN(Sheet1!E3) = 18
1st comma - =SEARCH(",",Sheet1!E3) = 5
1st line of address - =MID(Sheet1!E3,1,(C3-1)) = add1
2nd comma - =SEARCH(",",Sheet1!E3,(C3+1)) = 11
2nd line of address - =MID(Sheet1!E3,(C3+1),((E3-C3)-1)) = add2
3rd comma - =SEARCH(",",Sheet1!E3,(E3+1)) = 17
3rd line of address - =MID(Sheet1!E3,(E3+1),((G3-E3)-1)) = add3
postition of '.' - =SEARCH(".",Sheet1!E3) = 18
4th line of address - =MID(Sheet1!E3,(G3+1),((I3-G3)-1)) =
postcode - =MID(Sheet1!E3,(I3+1),((B3-I3))) =

PROBLEM is this works fine for the 1st and 2nd addresses in the example above BUT for the 3rd address i get '#VALUE!' errors for the 3rd comma, 3rd line of address and 4th line of address.
I have tried using the 'ERROR.TYPE' command - '=IF(ERROR.TYPE(H3)=3, "",H3)', and after reading the help file for this command i was expecting it to either populate the field with '' or the data from the split address, instead if the field contains '#VALUE', it populates with '', but if the field contains data 'add3' then it populates with '#N/A!' ? what i dont further understand is why when i use the same command '=IF(ERROR.TYPE(G15)=7,H3, ""' on the field that has just given the '#N/A' error it gives 'add3' !?



any ideas please?

Steve

Hi,

you could do some really clever formula here, and take weeks getting it working, or


you could take the simple approach, and for your data in column A, in B1 put

=LEFT(A1,FIND(",",A1)-1)
or, to error test
=IF(ISERROR(LEFT(A1,FIND(",",A1)-1)),"",LEFT(A1,FIND(",",A1)-1))

then in C1 put
=MID(A1,FIND(",",A1)+1,LEN(A1))
or, with error test
=IF(ISERROR(MID(A1,FIND(",",A1)+1,LEN(A1))),"",MID(A1,FIND(",",A1)+1,LEN(A1)))

then in D1 start again, but with the leftovers after extracting the first addr,
=TRIM(LEFT(C1,FIND(",",C1)-1))
or, with error test
=IF(ISERROR(LEFT(C1,FIND(",",C1)-1)),"",TRIM(LEFT(C1,FIND(",",C1)-1)))
then in E1
=MID(C1,FIND(",",C1)+1,LEN(C1))
with error
=IF(ISERROR(MID(C1,FIND(",",C1)+1,LEN(C1))),"",MID(C1,FIND(",",C1)+1,LEN(C1)))
repeat again for the 4th address

the postcode is =TRIM(MID(A1,FIND(".",A1)+1,99))

or you could write some VB code, but the above is quite simple to understand (though tedious)

After you have the addresses, copy the real columns and Paste Special = Values back over themselves, then delete the helper columns and column A.

hth
---