Software

Ask something
Discussion Locked

Trouble linking fields in Excel 97

0 helpful
2 replies

by shanghai sam

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Hi, I have got a really complex problem here and I don't know if this can be solved. I will go through the scenario. There are two files, lets say file A(the source file) and file B(update file). Now there are two fields which are linked and whenever information is entered on file B, it will pull up information from file A and enter it on file B. Now this is the difficult bit. People want to enter information differently on file B. i.e the field they are linking to on file A has no dashes in the entry i.e id no= 1234567 and the field they are linking from file B has dashes i.e id no = 123-45-67. Now is there a way of linking these fields together albeit they have the same information but one of the fields has dashes in them. Someone pleasehelp or just tell me it is impossible. Cheers Dave

2 replies

by aisling

1) if the dashes in the data entry field are always in the same place - put a custom format on that field so that users don't have to enter the dashes to see "123-45-67" this way the dashes don't form part of the data entered and can easily be linked back to file A

2) if not, have another column to eliminate dashes using the MID function (returning specified characters in text) and FIND function (to find the dashes and eliminate these characters from the MID function) This is a lot more complicated that option 1!

Good Luck

by l_lk

Hi, I assume that all id no have same lenght,e.g. 7 digit, and you want to have two dash after 3rd & 5th digit, you can set the "cell format" of file B by right click of you mouse and choose self define, and add a new format as : ###-##-##
and you will found that the content of the field will become 123-45-67, but if you only input 6 digit at file A (e.g. 123456), at file B, you will get a result : 12-34-56.(it will count from right to left, therefore if the digit different, it will present diff feature)

This discussion is closed to new replies.