lampel

string manipulation excel cell

13 posts in this topic

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.

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by lampel

Share this post


Link to post
Share on other sites
$sString = "car1 , car2 , car3, car4"
msgbox(0, '' , stringregexpreplace($sString , ",.*," , "-"))

 


,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
$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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Thank you so much for the time spent 

water and boththose.

water this is exactly what i needed.

 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

yeah that's brilliant...

i have a long way to go ^_^.

i need to explore that StringRegExpReplace a bit more.

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