Tech Support Guy banner
Status
Not open for further replies.

Script To Automatically Trim Text Data Into Several Lines

8K views 76 replies 3 participants last post by  OBP 
#1 ·
Hello. I work in a computer store in Indonesia as an administrator. My job is to create invoices. My crazy supervisor wants to see trimmed label address. She said it's more beautiful to look at -,-' Problem is the address I receive is always jumbled into one long line. Separating them (based on street address, etc) takes time. During the national shopping day, where we can have like 500 invoices in a day, I don't think I can handle it.

Below is one example of the address data that I have to work with:
17082509333FJ40
Alamat Pengiriman
Agus, 62812345678
Perumahan Orang Kaya, Jl.Jalan 2 BLOK H1/1001, Katulampa, Bogor Barat, KOTA BOGOR, BOGOR Barat - KOTA, JAWA BARAT, ID, 12345

The result I want is:
17082509333FJ40
Agus
Perumahan Orang Kaya
Jl.Jalan 2
BLOK H1/1001
Katulampa
Bogor Barat
KOTA BOGOR
12345
'0812345678

Of course, I understand it is impossible for a script to wholly replace man's logic. But if the script can help me create new lines based on several conditions, it will greatly help me. The conditions are as follow..
New line is made immediately before:
1. Word "Jalan" or "Jl." or "Jl"
2. Word "Perumahan" or "Perum"
3. Word "Blok" or "Block"
4. Word "RT" or "RT***" (*=number)
5. Word "Gang" or "Gg." or "Gg"
6. Word "Kel." or "Kel" or "Kelurahan"
7. Word "Kec." or "Kec" or "Kecamatan"
8. Word "Kota"
9. 5 random number (post code)

New line is made immediately after:
10. A comma and a space ", "

(NOTE1: IF the condition described above has already had a new line, the script doesn't need to re-add new line for that condition. In my example above, the word "Perumahan" starts the new line. Therefore there is no need for additional new line for that word)

(NOTE 2: The words described above doesn't necessarily starts with a capital letter. It can starts with a small letters. For example the word "perumahan" instead of "Perumahan"


Things to erase/replace
1. Word "ID"
2. A comma and a space ", "
3. Word "Alamat Pengiriman"
4. 62 is replaced with "'0" (yes, there is an ' inside)

Please teach me how to automatically sort the address data. I prefer to use WordPad. But if it's impossible, Notepad++ or MS Word is fine. Please help, thank you very much in advance for your time and willingness to help. God bless :)
 
See less See more
#74 ·
Wow you guys really worked hard on this seemingly impossible task.
Yeah hahaha... For a moment, I thought my thread was the longest here...

Congratulations hamtroid for coding some VBA!
:ROFLMAO::D

The best way to make it easier going forward is to require the address to parsed be in specific format(s). Reject the file if it doesn't conform to some standard. Else you will be constantly recoding.
This one is impossible because the address data comes from ecommerce website. The format depends on how the customers write it. Hence the need for parsing script o_O
 
#76 ·
@OBP That's insanely long :LOL: but then again, many replies there look more like chats than discussion. I'm glad mine hasn't gone that long hehehe...

I actually studied VB back in college in early 2000. But I forget ALL and have no time to start from zero. So if there is any cheat sheet for VBA Excel? If not, maybe complete tutorial? I'd be grateful. I want to be able to modify the parsing script you made for me, in case I bump into new problems.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top