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

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

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




.

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




.

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]