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