Remove any word from the sentence that contains a number

[Dossfm0q]

formula form array words to remove whole word that have numbers

=IFERROR(IF(FIND({0,1,2,3,4,5,6,7,8,9;0,1,2,3,4,5,6,7,8,9;0,1,2,3,4,5,6,7,8,9},MID(A2,FIND("|",SUBSTITUTE(" "&A2&" "," ","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))),FIND(" ",A2&" ",FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))))-FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))))),1)<>0,MID(A2,FIND("|",SUBSTITUTE(" "&A2&" "," ","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))),FIND(" ",A2&" ",FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))))-FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))))),""),"")

=MID(A2,FIND("|",SUBSTITUTE(" "&A2&" "," ","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))),FIND(" ",A2&" ",FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))))-FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))))

HHH.xlsx
	A	B	C
1	Text	Formula	Result
2	P1 FMD 15h		FMD
3		P1	X
NN

Cell Formulas
Range	Formula
B2	B2	=IFERROR(IF(FIND({0,1,2,3,4,5,6,7,8,9;0,1,2,3,4,5,6,7,8,9;0,1,2,3,4,5,6,7,8,9},MID(A2,FIND("|",SUBSTITUTE(" "&A2&" "," ","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))),FIND(" ",A2&" ",FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))))-FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))))),1)<>0,MID(A2,FIND("|",SUBSTITUTE(" "&A2&" "," ","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))),FIND(" ",A2&" ",FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))))-FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))))),""),"")
B3	B3	=MID(A2,FIND("|",SUBSTITUTE(" "&A2&" "," ","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)))),FIND(" ",A2&" ",FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))))-FIND("|",SUBSTITUTE(" "&A2&" "," ","|",(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sandy666]

another option

RawData		RawData
P1 FMD 15h		FMD

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"RawData", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "RawData"),
    Numbers = Table.AddColumn(Split, "Nr", each Text.Select([RawData], {"0".."9"})),
    Filter = Table.SelectRows(Numbers, each ([Nr] = "")),
    RC = Table.RemoveColumns(Filter,{"Nr"})
in
    RC

 

[Dossfm0q]

thank you for advance, great and perfect answer
But
unfortunately I am not familiar with Power Query, so I am waiting for anther answer with formula

 

[sandy666]

in case you've more than one word without number(s)

RawData		Result
P1 FMD 15h		FMD
ADE 23VX 24drt		ADE
aX 4ko By 55zR DD		aX By DD
12D ERR 5THC VAL		ERR VAL
LZ 1A DEF 2 C 5 QWA 6FF sandy sandy666		LZ DEF C QWA sandy

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    Split = Table.ExpandListColumn(Table.TransformColumns(Index, {{"RawData", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "RawData"),
    Numbers = Table.AddColumn(Split, "Nr", each Text.Select([RawData], {"0".."9"})),
    Filter = Table.SelectRows(Numbers, each ([Nr] = "")),
    Grp = Table.Group(Filter, {"Index"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Grp, "Result", each [Count][RawData]),
    Extract = Table.TransformColumns(List, {"Result", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    TSC = Table.SelectColumns(Extract,{"Result"})
in
    TSC

 

[sandy666]

unfortunately I am not familiar with Power Query, so I am waiting for anther answer with formula

sure, no problem
have a nice day

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as your version(s) determine what functions and resources are available to you. (Don’t forget to scroll down & ‘Save’)

Also, would a formula involving a user-defined function be acceptable?