How to remove the sentences from Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1378597%22%20slang%3D%22en-US%22%3EHow%20to%20remove%20the%20sentences%20from%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1378597%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%20all%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20a%20long%20text%20in%20the%20Excel%20cell.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20remove%20the%20sentence%20with%20the%20starting%20%22PMID%3A%20%22.%3C%2FP%3E%3CP%3EWhich%20formula%20I%20could%20use%20to%20remove%20the%20sentence%20highlighted%20in%20red%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%EF%BC%BB%E5%9B%9E%E7%AD%94%EF%BC%BD%3CBR%20%2F%3E%E7%99%8C%E8%85%AB%E5%88%A5%E3%81%AEMSI-high%E5%9B%BA%E5%BD%A2%E7%99%8C%E3%81%AE%E5%89%B2%E5%90%88%E3%81%AE%E5%9B%B3%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%E3%81%AF%E3%83%9A%E3%83%A0%E3%83%96%E3%83%AD%E3%83%AA%E3%82%BA%E3%83%9E%E3%83%96%E3%81%AE%E9%81%A9%E5%BF%9C%E3%81%AB%E3%81%AA%E3%82%8B%E6%82%A3%E8%80%85%E3%81%8C%E3%81%A9%E3%82%8C%E3%81%8F%E3%82%89%E3%81%84%E3%81%84%E3%82%8B%E3%81%AE%E3%81%8B%E4%BA%88%E6%B8%AC%E3%81%99%E3%82%8B%E3%81%9F%E3%82%81%E3%81%AB%E8%AA%BF%E6%9F%BB%E3%81%97%E3%81%9F%E6%96%87%E7%8C%AE%E3%81%AE%E8%A3%9C%E5%8A%A9%E7%9A%84%E3%81%AA%E3%83%87%E3%83%BC%E3%82%BF%E3%81%A7%E3%81%82%E3%82%8A%E3%80%81%E8%A7%A3%E6%9E%90%E3%81%8C%E8%A1%8C%E3%82%8F%E3%82%8C%E3%81%9F%E7%99%8C%E8%85%AB%E6%AF%8E%E3%81%AE%E7%97%87%E4%BE%8B%E3%81%AE%E6%AF%8D%E6%95%B0%E3%82%84%E3%80%81%E8%A9%B3%E7%B4%B0%E3%81%AA%E8%A7%A3%E6%9E%90%E6%96%B9%E6%B3%95%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6%E3%81%AE%E8%A8%98%E8%BC%89%E3%81%AF%E6%96%87%E7%8C%AE%E4%B8%AD(1)%E3%81%AB%E3%81%82%E3%82%8A%E3%81%BE%E3%81%9B%E3%82%93%E3%80%82%E3%81%93%E3%81%AE%E8%AB%96%E6%96%87%E3%81%AE%E3%82%B0%E3%83%A9%E3%83%95%E3%81%AF%E5%90%84%E7%99%8C%E8%85%AB%E3%81%AB%E3%81%8A%E3%81%91%E3%82%8BMSI-High%E6%82%A3%E8%80%85%E3%81%AE%E5%89%B2%E5%90%88%E3%81%AE%E5%8F%82%E8%80%83%E3%83%87%E3%83%BC%E3%82%BF%E3%81%A8%E3%81%97%E3%81%A6%E3%81%94%E5%8F%82%E7%85%A7%E3%81%8F%E3%81%A0%E3%81%95%E3%81%84%E3%80%82%3C%2FP%3E%3CP%3E%EF%BC%9C%E5%BC%95%E7%94%A8%EF%BC%9E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23FF0000%22%3E(1)PMID%3A%2028596308%3A%20Le%20DT%2C%20et%20al.%20Science.%202017%3B%20357%3A%20409-413.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1378597%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1378794%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20remove%20the%20sentences%20from%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1378794%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399400%22%20target%3D%22_blank%22%3E%40Tiffany_Jo%3C%2FA%3EIf%20the%20sentences%20you%20want%20to%20remove%20all%20begin%20with%20the%20same%20text%20-%20(1)PMID%2C%20then%20you%20could%20use%20Text%20to%20Columns%20on%20the%20Data%20tab%20to%20split%20the%20cell%20at%20that%20point.%20At%20then%20delete%20the%20new%20column%20created.%3C%2FP%3E%3CP%3EYou%20could%20also%20use%20Find%20and%20Replace%20to%20remove%20the%20content%20with%20that%20starting%20place%20using%20the%20wildcard%20characters%20-%20(1)PMID*%3C%2FP%3E%3CP%3EExcel%20formulas%20can%20only%20return%20values%20and%20not%20delete%20content.%20So%20a%20formula%20approach%20would%20only%20create%20a%20good%20cell.%20Not%20correct%20the%20original.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1378801%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20remove%20the%20sentences%20from%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1378801%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Thank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20would%20like%20to%20use%20the%20Find%20and%20Replace%2C%20could%20you%20please%20kindly%20tell%20how%20to%20judge%20the%20length%20of%20the%20sentence%20which%20starts%20with%26nbsp%3B%3CSPAN%3EPMID.%20I%20think%20the%20lengths%20are%20different.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1378815%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20remove%20the%20sentences%20from%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1378815%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399400%22%20target%3D%22_blank%22%3E%40Tiffany_Jo%3C%2FA%3ENo%20need.%20select%20the%20column%2C%20press%20Ctrl%20%2B%20H%20for%20Replace.%20Then%20use%20PMID*%20in%20the%20Find%20what%20and%20nothing%20in%20the%20Replace.%3C%2FP%3E%3CP%3EThe%20*%20is%20a%20wildcard%20character%20and%20will%20replace%20any%20text%20regardless%20of%20length%20after%20PMID.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1379090%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20remove%20the%20sentences%20from%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1379090%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F166840%22%20target%3D%22_blank%22%3E%40Alan%20Murray%3C%2FA%3E%26nbsp%3BThank%20you.%3C%2FP%3E%3CP%3EUsers%20want%20to%20replace%20them%20by%20using%20formulas.%3C%2FP%3E%3CP%3ECould%20you%20please%20give%20me%20some%20tips%20if%20you%20know%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1379145%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20remove%20the%20sentences%20from%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1379145%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399400%22%20target%3D%22_blank%22%3E%40Tiffany_Jo%3C%2FA%3ESure.%20You%20can%20pull%20all%20of%20the%20sentence%20up%20to%20the%20PMID%20with%20a%20combination%20of%20the%20LEFT%20and%20FIND%20functions.%3C%2FP%3E%3CP%3E%3CFONT%3E%3DLEFT(A1%2CFIND(%22PMID%22%2CA1)-1)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

hi all

 

There is a long text in the Excel cell.

I would like to remove the sentence with the starting "PMID: ".

Which formula I could use to remove the sentence highlighted in red?

 

[回答]
癌腫別のMSI-high固形癌の割合の図のデータはペムブロリズマブの適応になる患者がどれくらいいるのか予測するために調査した文献の補助的なデータであり、解析が行われた癌腫毎の症例の母数や、詳細な解析方法についての記載は文献中(1)にありません。この論文のグラフは各癌腫におけるMSI-High患者の割合の参考データとしてご参照ください。

<引用>
(1)PMID: 28596308: Le DT, et al. Science. 2017; 357: 409-413.

5 Replies
Highlighted

@Tiffany_JoIf the sentences you want to remove all begin with the same text - (1)PMID, then you could use Text to Columns on the Data tab to split the cell at that point. At then delete the new column created.

You could also use Find and Replace to remove the content with that starting place using the wildcard characters - (1)PMID*

Excel formulas can only return values and not delete content. So a formula approach would only create a good cell. Not correct the original.

Highlighted

Hi Thank you.

 

If I would like to use the Find and Replace, could you please kindly tell how to judge the length of the sentence which starts with PMID. I think the lengths are different.

Highlighted

@Tiffany_JoNo need. select the column, press Ctrl + H for Replace. Then use PMID* in the Find what and nothing in the Replace.

The * is a wildcard character and will replace any text regardless of length after PMID.

Highlighted

@Alan Murray Thank you.

Users want to replace them by using formulas.

Could you please give me some tips if you know?

Highlighted

@Tiffany_JoSure. You can pull all of the sentence up to the PMID with a combination of the LEFT and FIND functions.

=LEFT(A1,FIND("PMID",A1)-1)