Previously I have never had a problem using Calc reading Excel files but now I find email addresses are repeated when I use Calc. What would show is a grey highlighted address repeated several times, such as ‘emailaddress@gmail.comemailaddress@gmail.com’. However, if I click on the Excel file everything is fine. Any ideas? Could this be a problem with the new Calc?
First thought is that it’s reading both the link and the text that is linked, then putting them together. The flaw in that thought is generally there would be a mailto: in there somewhere. But, since it’s already misbehaving…
Are there active links in the Excel file? If you Remove Hyperlink and retry does the result change?
Thanks eye2brain for the suggestion. Now the Excel file has been downloaded and is a file on the computer. I highlighted the column with all the gray and doubled emails then went to file and clicked on default formatting. This removed the gray aspect and thus I think the hyperlink, however; there are still two of the same emails such as name@gmail.comname@gmail.com. As I said earlier if while in the mail system I click on the excel file in the email - everything is fine - no gray and no double emails. Could this be a problem with the originator of the Excel file and if so what did they do and how can I easily fix it?
Any other suggestions?
Don
Try to save the file from Excel as CSV and import that CSV file into Calc. Since CSV is plain text, the extras caused by tricky formatting will be discarded.
Again thank you JohnSUN for your suggestion. I followed it but it was a disaster - many lines of information were all jumbled up ie some information going from one column to another, mixing with original data and not always consistent. I would have thought your idea would have worked. Still looking for ideas to correct.
Don
Could you please upload a fragment of the original problem file? Real data can be replaced with virtual data.
Hi sokol92,
Sure, I have deleted most of the data and changed as much as possible but had to include my own email address as when I tried to change the address it changes it from hyperlink. With the partial list it shows the same as the original file.
Don
I opened your attachment in Excel and Calc, don’t see the difference. I expected to see a file that opens fine in Excel and has problems opening in Calc.
Cell K3 contains (Calc) a hyperlink with the URL “about:blank” and the display text “silverbulletspider@gmail.comsilverbulletspider@gmail.comsilverbulletspider@gmail.com”.
That is really strange - unfortunately I do not have Excel but only Libre Office Calc and it was version 4. So I deleted the program and installed the latest version 7.1.6.2 . Instead of greyed emails they are blue but the double and triple emails are still there. I then highlighted the emails and under format clicked clear direct formatting which changed them to black font. A little progress but still a problem which I do not understand.
Don
In Excel 2010 and in Calc I see the email address repeated three times.
Select the cells with hyperlinks and in Format Cells format as General.
Insert a column L after column K. In cell L3 enter =LEFT(K3,LEN(K3)/3) and drag down. You can then copy the new cells and Paste Special as Unformatted Text
Thank you EarnestAl, all went well and L3 changed from 3 to 1 email - good. However,I did not understand the ‘drag down’. I went on to copy K4 to L4 but still got 3 copies of email.
Also even if all the corrections can be made I really want to know why this happened in the first place as I have never had this problem before and do not want it to happen again.
Copy L3 to L4. Or grab the bottom right corner of L3 and drag down to copy down. When completed select column L, Copy, then Paste Special as Unformatted Text.
You might find the Calc Guide is a useful reference, download from Documentation
I cannot say how the tripling happened, but as it appears in the Excel spreadsheet in Excel then it must have happened before it was opened in Calc.
1 Like
Again many thanks for all your assistance, and the others who also made suggestions. Actually the tripling did not appear in the original Excel file as when I viewed it clicking in the original email all was good and only when I used Libre Office. I think the problem was the old Libre Office software which was 7.1.3.2 (64) as now with 7.1.5.2 (64) everything is fine. This was an interesting challenge and I learned several things as a result. Again thank you.
The problem is the string
silverbulletspider@gmail.comsilverbulletspider@gmail.comsilverbulletspider@gmail.com
is already stored in that file as is. I assume you used your problematic constellation to save that file, but as such it is not possible to inspect what the actual problem when loading the original file would be.