Jump to content
Sign in to follow this  
huskies

Excel separateing data in one cell

Recommended Posts

huskies

Is there a way using autoit's excel functions to extract the number infront of the phrase "units" ? and also the corresponding Name?

for example How do I have this in the format of PP002NumberA01 in column A2 and 16600 in column B2 so I can graph this info later, right now the entire row is in one cell.

PP002NumberA01 (9.75.6.04): 16600 Units

PP002NumberA02 (9.75.6.05): 18181 Units

PP002NumberA03 (9.75.6.06): 16648 Units

PP002NumberA04 (9.75.6.07): 16342 Units

PN002Number01 (45.67.07.162): 17638 Units

PN002Number02 (45.67.07.163): 15054 Units

PB002Number01 (45.67.07.34): 15301 Units

PB002Number02 (45.67.07.35): 15460 Units

PM001Number10 (45.67.07.75): 16182 Units

PM001Number11 (45.67.07.76): 13622 Units

PM001Number12 (45.67.07.77): 0 Units

PT001Number01 (45.67.100.3): 9515 Units

PT001Number02 (45.67.100.4): 11930 Units

PT001Number03 (45.67.100.5): 10676 Units

PT001Number04 (45.67.100.6): 8650 Units

PT001Number05 (45.67.100.7): 9501 Units

PT001Number06 (45.67.100.8): 11463 Units

PT005Number10 (45.67.07.139): 7571 Units

PN002ANumber03 (45.67.07.165): 28 Units


<It Shall Be Done>

Share this post


Link to post
Share on other sites
DaRam

StringSplit(Cell Contents, " ") will give you an Array to work with.

aValues = StringSplit("PP002NumberA01 (9.75.6.04): 16600 Units", " ")

aValues[1] will contain "PP002NumberA01", and

aValues[3] will contain "16600"

Is there a way using autoit's excel functions to extract the number infront of the phrase "units" ? and also the corresponding Name?

for example How do I have this in the format of PP002NumberA01 in column A2 and 16600 in column B2 so I can graph this info later, right now the entire row is in one cell.

Share this post


Link to post
Share on other sites
PsaltyDS

Is there a way using autoit's excel functions to extract the number infront of the phrase "units" ? and also the corresponding Name?

for example How do I have this in the format of PP002NumberA01 in column A2 and 16600 in column B2 so I can graph this info later, right now the entire row is in one cell.

Use a StringRegExp() to pull the parts you want:
#include <Array.au3>

Global $avInput[3] = ["PP002NumberA01 (9.75.6.04): 16600 Units", _
        "PP002NumberA02 (9.75.6.05):  Units", _; Corrupt entry for testing
        "PP002NumberA03 (9.75.6.06): 16648 Units"]
Global $avRegEx

For $n = 0 to UBound($avInput) - 1
    $avRegEx = StringRegExp($avInput[$n], "(\A[[:alnum:]]+)(?:.+\:\s)([[:digit:]]+)", 3)
    If IsArray($avRegEx) And (UBound($avRegEx) = 2) Then
        _ArrayDisplay($avRegEx, "String " & $n)
    Else
        MsgBox(16, "Error", "String " & $n & " was malformed:  " & $avInput[$n])
    EndIf
Next

This simulates just parsing the string. Pull the string with _ExcelReadCell(), and write the parts with _ExcelWriteCell().

:D

Edit: Rats, DaRam beat me to it. But his doesn't have a cool-geeky regex in it!

:o

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.