That I think is because readxl will choose one type per column, based on the first 1,000 entries (argument guess_max). So, if there is an cell with '10 (note the quote mark which makes it text and not numeric) in the first 1,000 rows, readxl will make a character column in the data frame. Here, you have Excel files where the first 1,000 entries are numeric, so readxl expects the column to be double, but then the entry on row 6,129 is text, so readxl will force the conversion to be consistent with the first 1,000 entries, but warns you because it could not be what you want.
No idea, look in the cell F:6,889 of your Excel sheet, you might find the reason.
Side-note: this is unrelated and not absolutely necessary, but I recommend you work with lists, which will make your life easier later:
sheet_names <- c("Maininfo", "Anthropometric")
all_excel_content <- map(sheet_names,
function(current_sheet) read_excel("Dr.Mee.xlsx", sheet = current_sheet))
all <- reduce(all_excel_content, left_join, by = "PID")
Else you need to type every left_join() call manually.