Separate Cell Address into Column Letter(s) and Row Number(s)

[FRED_SHEET]

I have the cell addresses listed for one of my Microsoft Excel Worksheets I would like to separate the cell address into the column letter and row number in separate cells.

In other words cell address A1 would be separated into A in one cell and 1 in another cell, cells with multiple letters AA and numbers 57 would be treated appropriately.

Is there a way of doing this? I was thinking of using an IF statement but don't know if that would work.

Any help would be appreciated. Thank You.

 

[Fluff]

How are you getting the address into the cell & what format is it in?

 

[FRED_SHEET]

I used a Macro to generate the Cell Address it's format is listed as general.

 

[Fluff]

In that case can you post the macro

 

[FRED_SHEET]

In that case can you post the macro

This is the macro I used it was posted on the internet by KutoolsforExcel.

Sub ListFormulas()
'Update 20141027
Dim Rng As Range
Dim WorkRng As Range
Dim xSheet As Worksheet
Dim xRow As Integer
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlFormulas, 23)
If WorkRng Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set xSheet = Application.ActiveWorkbook.Worksheets.Add
xSheet.Range("A1:C1") = Array("Address", "Formula", "Value")
xSheet.Range("A1:C1").Font.Bold = True
xRow = 2
For Each Rng In WorkRng
    xSheet.Cells(xRow, 1) = Rng.Address(RowAbsolute:=False, ColumnAbsolute:=False)
    xSheet.Cells(xRow, 2) = " " & Rng.Formula
    xSheet.Cells(xRow, 3) = Rng.Value
    xRow = xRow + 1
Next
xSheet.Columns("A:C").AutoFit
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Ok, that's putting the address in A2 downwards, where do you want the col & row to go?

 

[FRED_SHEET]

Ok, that's putting the address in A2 downwards, where do you want the col & row to go?

This Macro creates a new worksheet with all of formulas from a specific worksheet or worksheet range in a workbook. This new worksheet contains three columns:

A Address
B Formula
C Value

I would like to create two more columns D and E.

D Address Column Letter(s)
E Row Number(s)

I might be able to do this by modifying the Macro or I could use some type of formula to separate the Apha from the Numeric in Address.

It would be nice to know how to do both methods.

 

[Fluff]

Ok how about

Sub ListFormulas()
'Update 20141027
Dim Rng As Range
Dim WorkRng As Range
Dim xSheet As Worksheet
Dim xRow As Integer
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlFormulas, 23)
If WorkRng Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set xSheet = Application.ActiveWorkbook.Worksheets.Add
xSheet.Range("A1:E1") = Array("Address", "Formula", "Value", "Column", "Row")
xSheet.Range("A1:E1").Font.Bold = True
xRow = 2
For Each Rng In WorkRng
    xSheet.Cells(xRow, 1) = Rng.Address(RowAbsolute:=False, ColumnAbsolute:=False)
    xSheet.Cells(xRow, 2) = " " & Rng.Formula
    xSheet.Cells(xRow, 3) = Rng.Value
    xSheet.Cells(xRow, 4) = Split(Rng.Address(1, 0), "$")(0)
    xSheet.Cells(xRow, 5) = Split(Rng.Address(1, 0), "$")(1)
    xRow = xRow + 1
Next
xSheet.Columns("A:C").AutoFit
Application.ScreenUpdating = True
End Sub

 

[FRED_SHEET]

Ok how about

Sub ListFormulas()
'Update 20141027
Dim Rng As Range
Dim WorkRng As Range
Dim xSheet As Worksheet
Dim xRow As Integer
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlFormulas, 23)
If WorkRng Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set xSheet = Application.ActiveWorkbook.Worksheets.Add
xSheet.Range("A1:E1") = Array("Address", "Formula", "Value", "Column", "Row")
xSheet.Range("A1:E1").Font.Bold = True
xRow = 2
For Each Rng In WorkRng
    xSheet.Cells(xRow, 1) = Rng.Address(RowAbsolute:=False, ColumnAbsolute:=False)
    xSheet.Cells(xRow, 2) = " " & Rng.Formula
    xSheet.Cells(xRow, 3) = Rng.Value
    xSheet.Cells(xRow, 4) = Split(Rng.Address(1, 0), "$")(0)
    xSheet.Cells(xRow, 5) = Split(Rng.Address(1, 0), "$")(1)
    xRow = xRow + 1
Next
xSheet.Columns("A:C").AutoFit
Application.ScreenUpdating = True
End Sub

Fantastic that did the job. Thanks very much for your help!!

 

[Fluff]

You're welcome & thanks for the feedback.