Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting the column letter and row number
Hey
What code will extract only the column letter and then row number from the below sample data? Sheet1!$K$8 The desired outcome is a variable that will store K and another variable that will store 8 Sheet1!$AC$108 The desired outcome is a variable that will store AC and another variable that will store 108 Sheet1!$F$92 The desired outcome is a variable that will store F and another variable that will store 92 Thanks in advance. Todd Huttenstine |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting the column letter and row number
Hi Todd
why do you want the column letter. It would be easier to use the column index?. But anyway: 1. Row number: =ROW(Sheet1!$K$8) 2. Column letter =SUBSTITUTE(ADDRESS(1,COLUMN(AC1),4),1,"") -- Regards Frank Kabel Frankfurt, Germany "Todd Huttenstine" schrieb im Newsbeitrag ... Hey What code will extract only the column letter and then row number from the below sample data? Sheet1!$K$8 The desired outcome is a variable that will store K and another variable that will store 8 Sheet1!$AC$108 The desired outcome is a variable that will store AC and another variable that will store 108 Sheet1!$F$92 The desired outcome is a variable that will store F and another variable that will store 92 Thanks in advance. Todd Huttenstine |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting the column letter and row number
Hey Frank thanks for the response, however the value
Sheet1!$AC$108 is stored in a variable in a userform module. How would I extract from this? Thanks Todd Huttenstine -----Original Message----- Hi Todd why do you want the column letter. It would be easier to use the column index?. But anyway: 1. Row number: =ROW(Sheet1!$K$8) 2. Column letter =SUBSTITUTE(ADDRESS(1,COLUMN(AC1),4),1,"") -- Regards Frank Kabel Frankfurt, Germany "Todd Huttenstine" schrieb im Newsbeitrag ... Hey What code will extract only the column letter and then row number from the below sample data? Sheet1!$K$8 The desired outcome is a variable that will store K and another variable that will store 8 Sheet1!$AC$108 The desired outcome is a variable that will store AC and another variable that will store 108 Sheet1!$F$92 The desired outcome is a variable that will store F and another variable that will store 92 Thanks in advance. Todd Huttenstine . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting the column letter and row number
Hi
one way: use InStr and Mid to extract the part between the two $ signs -- Regards Frank Kabel Frankfurt, Germany "Todd Huttenstine" schrieb im Newsbeitrag ... Hey Frank thanks for the response, however the value Sheet1!$AC$108 is stored in a variable in a userform module. How would I extract from this? Thanks Todd Huttenstine -----Original Message----- Hi Todd why do you want the column letter. It would be easier to use the column index?. But anyway: 1. Row number: =ROW(Sheet1!$K$8) 2. Column letter =SUBSTITUTE(ADDRESS(1,COLUMN(AC1),4),1,"") -- Regards Frank Kabel Frankfurt, Germany "Todd Huttenstine" schrieb im Newsbeitrag ... Hey What code will extract only the column letter and then row number from the below sample data? Sheet1!$K$8 The desired outcome is a variable that will store K and another variable that will store 8 Sheet1!$AC$108 The desired outcome is a variable that will store AC and another variable that will store 108 Sheet1!$F$92 The desired outcome is a variable that will store F and another variable that will store 92 Thanks in advance. Todd Huttenstine . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting the column letter and row number
Hi Todd,
Todd Huttenstine wrote: What code will extract only the column letter and then row number from the below sample data? Hey Frank thanks for the response, however the value Sheet1!$AC$108 is stored in a variable in a userform module. How would I extract from this? These functions may work for you: Public Function glGetRowNumber(rsAddress As String) As Long On Error Resume Next glGetRowNumber = Range(rsAddress).Row On Error GoTo 0 End Function Public Function gsGetColLetter(rsAddress As String) As String On Error Resume Next gsGetColLetter = Split(Range(rsAddress _ ).Address(True, False), "$")(0) On Error GoTo 0 End Function -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I turn excel columns from column number to column letter? | Setting up and Configuration of Excel | |||
Column() to return a letter instead of a number? | Excel Worksheet Functions | |||
column header changed from letter to number, how return to letter | Excel Discussion (Misc queries) | |||
Auto number w/ different letter-number combos in same column | Excel Worksheet Functions | |||
Column Number/Letter | Excel Programming |