Need vba : Extract data after mark , (comma)

[muhammad susanto]

hello all,

perhaps, you can help me how to replace or extract data after mark , (comma) using vba code

like see this :

sample raw	after macro
Alam Subur, CV/Komala Budiman	Alam Subur
Genteng Garuda Riau Agung, CV	Genteng Garuda Riau Agung
Husni, M/Toko Sepatu Bata	Husni
Indah Jaya, PT	Indah Jaya
etc...

<tbody>
</tbody>

for anything for me, thanks in advance...

m.susanto

 

[muhammad susanto]

ups, i missing should be extract data before mark , (comma)

 

[hiker95]

muhammad susanto,

Sample raw data:

Excel 2007
	A	B
1	Alam Subur, CV/Komala Budiman
2	Genteng Garuda Riau Agung, CV
3	Husni, M/Toko Sepatu Bata
4	Indah Jaya, PT
5
Sheet1

After the macro:

Excel 2007
	A	B
1	Alam Subur, CV/Komala Budiman	Alam Subur
2	Genteng Garuda Riau Agung, CV	Genteng Garuda Riau Agung
3	Husni, M/Toko Sepatu Bata	Husni
4	Indah Jaya, PT	Indah Jaya
5
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.

Option Explicit
Sub ExtractBeforeComma()
' hiker95, 11/11/2013
' http://www.mrexcel.com/forum/excel-questions/738486-need-visual-basic-applications-extract-data-after-mark-comma.html
Dim a As Variant, b As Variant
Dim i As Long, s
a = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
For i = 1 To UBound(a, 1)
  If InStr(Trim(a(i, 1)), ",") > 0 Then
    s = Split(a(i, 1), ",")
    b(i, 1) = s(0)
  End If
Next i
Range("B1").Resize(UBound(b, 1), UBound(b, 2)) = b
Columns(2).AutoFit
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

Then run the ExtractBeforeComma macro.

 

[muhammad susanto]

thank you very much, hiker95

now, i am very happy.....

 

[hiker95]

muhammad susanto,

You are very welcome. Glad I could help.

And, come back anytime.