Jump to content
lampel

string manipulation excel cell

Recommended Posts

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.

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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 by lampel

Share this post


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

 


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

Share this post


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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
iamtheky

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


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

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
lampel

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
lampel

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

×