Extract Text Before Word "Golongan"

[muhammad susanto]

hi all..

i have problem, how to extract text before word "Golongan"
like this

Excel 2007 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD]DATA/TEXT[/TD]
[TD]EXTRACT[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]Rumah Negara Golongan I Tipe C Permanen[/TD]
[TD]Rumah Negara[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]Rumah Negara Golongan I Tipe B Permanen[/TD]
[TD]Rumah Negara[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]Bangunan Gedung Kantor Permanen[/TD]
[TD]Bangunan Gedung Kantor Permanen[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]Pagar Permanen[/TD]
[TD]Pagar Permanen[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

anyone would help me..

m.susanto

 

[admiral100]

Hi,

Try This
=IFERROR(LEFT(A1,FIND("Golongan",A1)-1),A1)

 

[Rick Rothstein]

Here is another formula that you can try...

=LEFT(A1,FIND("Golongan",A1&"Golongan")-1)

Note: One difference between my formula and the one admiral100 posted appears when A1 is blank... my formula displays nothing for it whereas admira100's formula displays 0.

 

[muhammad susanto]

Hi,

Try This
=IFERROR(LEFT(A1,FIND("Golongan",A1)-1),A1)

wow...working great...thanks a lot..

 

[shuklaankur281190]

Here is another formula that you can try...

=LEFT(A1,FIND("Golongan",A1&"Golongan")-1)

Note: One difference between my formula and the one admiral100 posted appears when A1 is blank... my formula displays nothing for it whereas admira100's formula displays 0.

Rick can you please let me know why did you add "golongan" with A1 for finding the same? If we don't do the same thus it will retrieve result.

 

[shuklaankur281190]

or can try

=TRIM(LEFT(SUBSTITUTE(A1,"Golongan",REPT(" ",99)),99))

 

[Aladin Akyurek]

It's probably better not to be case sensitive here:

=IFERROR(LEFT(A1,SEARCH("Golongan",A1&"Golongan")-1),A1)

as you would not want to miss any golongan.

 

[shuklaankur281190]

It's probably better not to be case sensitive here:

=IFERROR(LEFT(A1,SEARCH("Golongan",A1&"Golongan")-1),A1)

as you would not want to miss any golongan.

@Aladin can you please let me know why did you add "golongan" with A1 for finding the same? If we don't do the same thus it will retrieve result.

 

[Rick Rothstein]

Rick can you please let me know why did you add "golongan" with A1 for finding the same? If we don't do the same thus it will retrieve result.

It allows me to eliminate the IFERROR function call. The reason for the IFERROR is to protect against the error the FIND function generates when it cannot find the text it is looking for... by concatenating the word onto A1, it guarantees the FIND function will always find it. If the searched for text exists in the cell, FIND will find it first... but if the searched for text is not in the cell, FIND will find the one I added at the end and use that to return the full contents of the cell (what your examples indicated you wanted).

It's probably better not to be case sensitive here:

=IFERROR(LEFT(A1,SEARCH("Golongan",A1&"Golongan")-1),A1)

as you would not want to miss any golongan.

Since you did like I did and concatenated the the word onto A1 inside the SEARCH function, what error is the IFERROR protecting against?

 

[shuklaankur281190]

It allows me to eliminate the IFERROR function call. The reason for the IFERROR is to protect against the error the FIND function generates when it cannot find the text it is looking for... by concatenating the word onto A1, it guarantees the FIND function will always find it. If the searched for text exists in the cell, FIND will find it first... but if the searched for text is not in the cell, FIND will find the one I added at the end and use that to return the full contents of the cell (what your examples indicated you wanted).




Since you did like I did and concatenated the the word onto A1 inside the SEARCH function, what error is the IFERROR protecting against?

Thank you @Rik