Excel separateing data in one cell

[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

[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.

[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().

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

Edited April 1, 2009 by PsaltyDS