Put string certainty from statement

muhammad susanto

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
2,077
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
hi...

how to pull text "Pekanbaru" in this data below :

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]data[/TD]
[TD]desired result[/TD]
[/TR]
[TR]
[TD]JALAN RIAU NO. 38B, KOTA PEKANBARU,,,,[/TD]
[TD]Pekanbaru[/TD]
[/TR]
[TR]
[TD]Jalan Dr. Sutomo Nomor 62, Pekanbaru,Pekanbaru,PEKAN BARU KOTA,PEKAN BARU,R I A U[/TD]
[TD]Pekanbaru[/TD]
[/TR]
[TR]
[TD]Jl. Beringin (gobah) Perum guru SD 10 Kec. Sail Pekanbaru,Sail,SAIL,PEKAN BARU,R I A U[/TD]
[TD]Pekanbaru[/TD]
[/TR]
[TR]
[TD]PERUMNAS POLTAK BROTHERS H NO. 10 KULIM,KULIM,BUKIT RAYA,PEKAN BARU,R I A U[/TD]
[TD]Pekanbaru[/TD]
[/TR]
</tbody>[/TABLE]

any assistance, much appreciated..

m.susanto
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
muhammad susanto,

Here is a macro solution for you to consider.

Sample raw data in the active sheet:


Excel 2007
AB
1datadesired result
2JALAN RIAU NO. 38B, KOTA PEKANBARU,,,,
3Jalan Dr. Sutomo Nomor 62, Pekanbaru,Pekanbaru,PEKAN BARU KOTA,PEKAN BARU,R I A U
4Jl. Beringin (gobah) Perum guru SD 10 Kec. Sail Pekanbaru,Sail,SAIL,PEKAN BARU,R I A U
5PERUMNAS POLTAK BROTHERS H NO. 10 KULIM,KULIM,BUKIT RAYA,PEKAN BARU,R I A U
6
Sheet1


After the macro:


Excel 2007
AB
1datadesired result
2JALAN RIAU NO. 38B, KOTA PEKANBARU,,,,Pekanbaru
3Jalan Dr. Sutomo Nomor 62, Pekanbaru,Pekanbaru,PEKAN BARU KOTA,PEKAN BARU,R I A UPekanbaru
4Jl. Beringin (gobah) Perum guru SD 10 Kec. Sail Pekanbaru,Sail,SAIL,PEKAN BARU,R I A UPekanbaru
5PERUMNAS POLTAK BROTHERS H NO. 10 KULIM,KULIM,BUKIT RAYA,PEKAN BARU,R I A UPekanbaru
6
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Compare Text
Sub Pull_Pekanbaru()
' hiker95, 09/13/2015, ME884929
Dim c As Range
Application.ScreenUpdating = False
With ActiveSheet
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    If InStr(c, "Pekanbaru") Or InStr(c, "Pekan Baru") Then
      c.Offset(, 1).Value = "Pekanbaru"
    End If
  Next c
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Pull_Pekanbaru macro.
 
Last edited:
Upvote 0
hiker95 said:
muhammad susanto,

Here is a macro solution for you to consider.

Sample raw data in the active sheet:

Excel 2007
AB

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]data[/TD]
[TD="bgcolor: #FFFFFF"]desired result[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]JALAN RIAU NO. 38B, KOTA PEKANBARU,,,,[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Jalan Dr. Sutomo Nomor 62, Pekanbaru,Pekanbaru,PEKAN BARU KOTA,PEKAN BARU,R I A U[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]Jl. Beringin (gobah) Perum guru SD 10 Kec. Sail Pekanbaru,Sail,SAIL,PEKAN BARU,R I A U[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]PERUMNAS POLTAK BROTHERS H NO. 10 KULIM,KULIM,BUKIT RAYA,PEKAN BARU,R I A U[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



After the macro:

Excel 2007
AB

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]data[/TD]
[TD="bgcolor: #FFFFFF"]desired result[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]JALAN RIAU NO. 38B, KOTA PEKANBARU,,,,[/TD]
[TD="bgcolor: #FFFFFF"]Pekanbaru[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Jalan Dr. Sutomo Nomor 62, Pekanbaru,Pekanbaru,PEKAN BARU KOTA,PEKAN BARU,R I A U[/TD]
[TD="bgcolor: #FFFFFF"]Pekanbaru[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]Jl. Beringin (gobah) Perum guru SD 10 Kec. Sail Pekanbaru,Sail,SAIL,PEKAN BARU,R I A U[/TD]
[TD="bgcolor: #FFFFFF"]Pekanbaru[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]PERUMNAS POLTAK BROTHERS H NO. 10 KULIM,KULIM,BUKIT RAYA,PEKAN BARU,R I A U[/TD]
[TD="bgcolor: #FFFFFF"]Pekanbaru[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Compare Text
Sub Pull_Pekanbaru()
' hiker95, 09/13/2015, ME884929
Dim c As Range
Application.ScreenUpdating = False
With ActiveSheet
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    If InStr(c, "Pekanbaru") Or InStr(c, "Pekan Baru") Then
      c.Offset(, 1).Value = "Pekanbaru"
    End If
  Next c
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Pull_Pekanbaru macro.
Click to expand...

hi hiker95, working run 100%

thanks a lot...
 
Upvote 0
muhammad susanto,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0
You must log in or register to reply here.

Similar threads

muhammad susanto
  • Question Question
Extract From Text After Comma Mark
Replies
2
Views
966
XOR LX
XOR LX
muhammad susanto
  • Question Question
Extract Address After Second Mark (,) From Left
Replies
9
Views
478
pgc01
P

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top