1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Script To Automatically Trim Text Data Into Several Lines

Discussion in 'Business Applications' started by hamtaroid, Aug 26, 2017.

Advertisement
  1. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,326
    Here is the modified version.
    (weird because I thought I posted it this morning)
     

    Attached Files:

    hamtaroid likes this.
  2. hamtaroid

    hamtaroid Castielus Thread Starter

    Joined:
    Aug 26, 2017
    Messages:
    22
    Is it possible to create IF statement for that? Something like:
    If there's something before Jl/Jalan, Jl/Jalan moves downward to line 3
    Else Jl/Jalan stays in line 2?

    Because almost 95% address I encounter, there's nothing before Jl/Jalan.

    Yes, the "No." now looks very pretty. Thank you OBP (y)

    OBP is such an angel and also a computer pro at the same time (y) He's a lifesaver!
    I did go to a similar forum - E***** E******* - before stumbling here. Only one replied and he implied that I had to pay for the service. He only gave me a free tip but it's very vague :cry: That's really a useless forum :confused:
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,326
    It is not quite that simple unless the next line is empty, otherwise all the lines need to be moved down, but how does that fit in with your Invoice that you posted?
     
    hamtaroid likes this.
  4. hamtaroid

    hamtaroid Castielus Thread Starter

    Joined:
    Aug 26, 2017
    Messages:
    22
    How about switching them? Is it possible?
    So Jl/Jalan gets line 2. If there's something before Jl/Jalan, they get line 3 or 4?

    If the above is impossible, maybe whatever before Jl/Jalan should be ignored? That way Jl/Jalan will still get line 2. But that means I have to manually paste it below. Is that possible?
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,326
    It is fairly simple providing that line 3 can be blank most of the time.
     
    hamtaroid likes this.
  6. hamtaroid

    hamtaroid Castielus Thread Starter

    Joined:
    Aug 26, 2017
    Messages:
    22
    Sorry, I've missed a very important part of your script... I just realize that you have 10 lines for address. I exclude the line for order number because I only paste the address label starting from Line Name until Line Telephone. Therefore it should be 9 lines for the address.

    Line 0: Order number (I don't count)
    Line 1: Name
    Line 2-5: Address (moveable)
    Line 6: District/subdistrict
    Line 7: City
    Line 8: post code
    Line 9: phone number
    Only line 2-5 are moveable. The others are stationary.

    I don't think line 3 can be blank most of the time. But Line 2-5 are moveable. You can switch the data around as long as they are in that line.
     
    Last edited: Sep 14, 2017
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,326
    Well, this extra line at the start may be a problem especially if it occurs with an address like this one
    170907034432E89
    Febriana
    Jl. Raya Waria
    Gg. Pak Raden No. 07
    RT. 03 RW. 02
    Kelurahan Singasari
    BOGOR UTARA - KOTA
    KOTA BOGOR
    16146
    '080759998804
    where there is no space left at all.
    Moving lines around is not that easy to program, which is why I went for the fixed positions for each line.
    The question is, is that extra first line necessary for the address to work?
    I will return the address to 9 lines.
     
    hamtaroid likes this.
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,326
    Here is the version without the blank line.
     

    Attached Files:

    hamtaroid likes this.
  9. hamtaroid

    hamtaroid Castielus Thread Starter

    Joined:
    Aug 26, 2017
    Messages:
    22
    Which extra first line? The line for order number? Or the extra line you placed in previous version?
    If you mean, order number line, yes it's very important.
    If you mean is the extra line you placed which counted total 10 lines, no. Because it moves down the entire content. So far this version is the best :D

    Oh, I have other question. When I paste data from the ecommerce website onto your excel sheet, the data is pasted in a vertically long column. Distorting everything. I have to manually select Paste Special then text. Any express way to achieve that special text paste without clicking this and that?
     
    Last edited: Sep 15, 2017
  10. hamtaroid

    hamtaroid Castielus Thread Starter

    Joined:
    Aug 26, 2017
    Messages:
    22
    Errr... Is there any space left for parsing product data? It's fairly short. And more simple...
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,326
    The extra line is the part of the address before jl, ie PT Indomarch Pertamax,
    What does the product data look like?

    You should be able to simply Record the Paste special actions to assign a separate macro for that.
     
  12. hamtaroid

    hamtaroid Castielus Thread Starter

    Joined:
    Aug 26, 2017
    Messages:
    22
    Oh that one.... It's somewhat important. BUT I can paste manually. The most important thing is the Jl/Jalan. It has to stay in line 2. Because when the "extra line" is absent, it will look weird for Jl/Jalan to get line 3.


    Product basically has 2 forms: with bonus and without. Bonus price is always zero.


    VERSION WITH BONUS:
    from:
    Seagate BackUp Plus Slim 1TB 2.5"USB 3.0 + Free Pouch, Antivirus McAfee
    Variasi: Silver
    815000.00


    to:
    Seagate BackUp Plus Slim 1TB 2.5"USB 3.0 Silver
    Free Pouch, Antivirus McAfee
    815000
    0



    VERSION WITHOUT BONUS:

    from:
    SSD WD Green 120GB 2.5" SATA
    761000.00


    to:
    SSD WD Green 120GB 2.5" SATA
    761000


    The cue for bonus is the sign + and the word FREE or GRATIS.
    Is it possible to cram it into the script?
     
  13. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,326
    You have said what the differences are, but not how it comes in, where on the spreadsheet or where it goes and in what format.
     
  14. hamtaroid

    hamtaroid Castielus Thread Starter

    Joined:
    Aug 26, 2017
    Messages:
    22
    Is it possible to paste the product under the address? So, the last line of the address is phone number line. Maybe you can add one blank line underneath before jumping to the product?
     
  15. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,326
    You are the one doing the pasting, so perhaps you can provide an example.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...

Short URL to this thread: https://techguy.org/1195292