Text position within cell

[Kamran01]

Dear All,

Please help me to know, how can i find text position within cells.

Sentence is, "Election commission has organized the Election 2018."

There are two "Election" and want to know both locations separately suppose first election location is 1 and second "Election" location is 38.

Thanks in advance,

Best Regard,
Kamran Noor

 

[Yongle]

with
C1 = "Election"
D1 = "Election commission has organized the Election 2018"

=SEARCH("find this", "search this text", start at this position)

FIRST
=SEARCH(C1,D1,1)
SECOND
=SEARCH(C1,D1,SEARCH(C1,D1,1)+LEN(C1))
THIRD
=SEARCH(C1,D1,SEARCH(C1,D1,SEARCH(C1,D1,1)+LEN(C1))+LEN(C1))
etc

Start position is the previous found position + number of characters in search string

 

[sandy666]

or
1st: =IFERROR(SEARCH("Election",A1,1),"")
2nd: =IFERROR(SEARCH("Election",A1,2),"")
3rd: =IFERROR(SEARCH("Election",A1,SEARCH("Election",A1,2)+1)-1,"")

 

[Kamran01]

Dear Yongle,

Thank you for these but if i want to know how many election in this sentence than?

I used this function and got answer, but don't know how to check counts of this sentence in the cell.

=SEARCH(A8,A4,SEARCH(A8,A4,SEARCH(A8,A4)+1)+1)

 

[sandy666]

try: =(LEN(A1)-LEN(SUBSTITUTE(A1,"Election","")))/LEN("Election")
A1 contain your text string

 

[Peter_SSs]

For the original question, try this formula copied across and down to get all the positions.

Note that if you are looking for the "word" election, the earlier formula suggestions fail for the sample text in my row 3 below because the text "election" is contained within the word "selection"

Excel Workbook
	A	B	C	D	E	F
1	Election commission has organized the Election 2018	1	39
2	The election in 1998 was the closest election since the election of 1977 although the election of 1994 was also close	5	38	57	87
3	The selection of the election date is done by the electoral commission	22
Positions (2)

 

[sandy666]

That's right Peter

If OP want to count Election in whole column (eg. table in post#6) PowerQuery can be used. It will count all (separated) Election in this column (count: 7)

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Changed Type1",{{"Column1", Text.Upper, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Uppercased Text", each ([Column1] = "ELECTION")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Column1"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"[/SIZE]

 

[Peter_SSs]

If OP want to count Election in whole column (eg. table in post#6) PowerQuery can be used.

I haven't seen that request in this thread, but if it was required, I'd still use the much shorter & simpler to implement (to me anyway) standard worksheet formula

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN(SUBSTITUTE(UPPER(" "&A1:A3&" ")," ELECTION ","")))/10

 

[sandy666]

I haven't seen that request in this thread, but if it was required, I'd still use the much shorter & simpler to implement (to me anyway) standard worksheet formula

Sure, but we've different point of view what is simpler

btw. column can contain 1 row also

All is good what works good

 

[Peter_SSs]

Thank you for these but if i want to know how many election in this sentence than?

To deal with upper/lower case issues and to ensure whole words as mentioned in post 6, I would use this version to count in a sentence.

Excel Workbook
	A	B
1	Election commission has organized the Election 2018	2
2	The election in 1998 was the closest election since the election of 1977 although the election of 1994 was also close	4
3	The selection of the election date is done by the electoral commission	1
Positions (3)