string manipulation excel cell

[lampel]

hello all,

i am a beginner and i am trying to do a manipulation on an excel cell 

Cell: "E35 E36 E37 E38"  --------> Output: "E35-E38"

Cell: "C1 C2 C3 C5 C6 C7 C8 C12"  --------> Output: "C1-C3" (jump to next cell ) , "C5-C8"(jump to next cell ) , "C12"

Cell: "R1 R3 R5 R6 R7"  --------> Output: "R1" (jump to next cell ) , "R3"(jump to next cell ) , "R5-R7"

i am trying to use StringSplite function + if value is larger then 1 then jump next cell

but i cant figure how can i do it ?

thanks lampel.

[Jfish]

Start with the UDF functions for Excel (see the help file for details under UDF).  There is one called _Excel_RangeRead that will read a range of cells into an array where you can apply your logic.  You can use _Excel_RanegWrite to put values back.  Also see the Wiki for some nice examples on Excel from @water.

Edited August 6, 2015 by Jfish

[water]

If I understand your post correctly you read a string from a single cell with cells references ("C1 C2 C3 C5 C6 C7 C8 C12") from Excel and want to output ranges ("C1-C3, C5-C8, C12").
Is this correct?

[lampel]

i read a string from a single cell it can be like "car1 , car2 , car3, car4"

and on the same cell i want to replace the string to "car1-car4"

Edited August 6, 2015 by lampel

[iamtheky]

$sString = "car1 , car2 , car3, car4"
msgbox(0, '' , stringregexpreplace($sString , ",.*," , "-"))

 

[water]

Something for you to play with:

; $sIn = "E35 E36 E37 E38"
; $sIn = "C1 C2 C3 C5 C6 C7 C8 C12"
$sIn = "R1 R3 R5 R6 R7"
$aCells = StringSplit($sIn, " ")
$bRangeStart = True
$iPrevious = 0
$sPrevious = ""
For $i = 1 To $aCells[0]
    $aSplit = StringRegExp($aCells[$i], "(\D*)(\d*)", 1)
    If $bRangeStart Then
        $sRangeStart = $aSplit[0] & Number($aSplit[1])
        $bRangeStart = False
    Else
        If $aSplit[0] <> $sPrevious Or Number($aSplit[1]) > $iPrevious + 1 Then
            $sRangeEnd = $sPrevious & $iPrevious
            If $sRangeStart = $sRangeEnd Then
                ConsoleWrite("Range: " & $sRangeStart & @CRLF)
            Else
                ConsoleWrite("Range: " & $sRangeStart & "-" & $sRangeEnd & @CRLF)
            EndIf
            $bRangeStart = True
            $iPrevious = 0
            $sPrevious = ""
            $sRangeStart = $aSplit[0] & Number($aSplit[1])
            $bRangeStart = False
        EndIf
    EndIf
    $sPrevious = $aSplit[0]
    $iPrevious = Number($aSplit[1])
Next
If $bRangeStart = False Then $sRangeEnd = $sPrevious & $iPrevious
If $sRangeStart = $sRangeEnd Then
    ConsoleWrite("Range: " & $sRangeStart & @CRLF)
Else
    ConsoleWrite("Range: " & $sRangeStart & "-" & $sRangeEnd & @CRLF)
EndIf

 

[water]

$sString = "car1 , car2 , car3, car4"
msgbox(0, '' , stringregexpreplace($sString , ",.*," , "-"))

 

My understanding is that "car1 , car2 , car4, car5"" should become "car1 - car2, car4- car5".
This doesn't work with your code.

[iamtheky]

i totally didnt read the OP and just answered #4.

[water]

I thought there was some REGEXP magic to reduce it to a single line

[lampel]

Thank you so much for the time spent 

water and boththose.

water this is exactly what i needed.

 

[water]

My script even works with something like this: A1 B2 C3 C4 C5 D1 and returns
Range: A1
Range: B2
Range: C3-C5
Range: D1

[water]

Glad you like the solution
Use _Excel_RangeRead and _Excel_RangeWrite to interact with Excel.

[lampel]

yeah that's brilliant...

i have a long way to go .

i need to explore that StringRegExpReplace a bit more.