Macro to copy only the numbers on a cell and making them numbers the Sheet Name

[ronanbaker1]

Hi Guys,

I nearly have my Macro finished stuck on one thing.

I want to make Cell A4 my Sheet Name.

Cell A4 contains both Letters and Numbers

Cell A4 may start with a word eg 'property: 165666'

I Just was the Number 165666 as my sheet name.

Also After the property number I want the Letters ' ISCOMP' as the Sheet name

' ISCOMP' will not change and so I don't think that part will be hard.

 

[VoG]

Try

Sub test()
With Range("A4")
    .Parent.Name = Right(.Value, Len(.Value) - InStr(.Value, " ")) & " ISCOMP"
End With
End Sub

 

[hippiehacker]

another approach could be using UDF which you can use as a formula in your sheets as well

Sub ronanbaker1()
ActiveSheet.Name = ExtractNumber(Cells(1, 4)) & " ISCOMP"
End Sub


Function ExtractNumber(sText As String)
    Dim iCount As Integer, i As Integer
    Dim lNum As String
     
    For iCount = Len(sText) To 1 Step -1
        If IsNumeric(Mid(sText, iCount, 1)) Then
            i = i + 1
            lNum = Mid(sText, iCount, 1) & lNum
        End If
        If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
    Next iCount
     
    ExtractNumber = CLng(lNum)
End Function

 

[ronanbaker1]

Hi Vog,

Very good Macro only thing is my property name is after the Property Number and Worksheets have a limit to 25 letters (+ my ISCOMP) so I would need it to skip over all the letters in cell a4.

ie cell a4 could = Property: 166620 North Side Church Road Killybegs

Or maybe a macro that will stop after the 25th letter Ie '166620 North Side Church Ro ISOMP'

Hi Hippiehacker,

Altthough I do think that you have what I am looking for I am finding it very hard to follow as it seems like a lot of detail.

 

[hippiehacker]

its quite easy to understand the difficult thing is the UDF which is Checking in back order your string and will place it in string(lNum) after checking all characters the string(lNum) will be returned as result of the function.

the sub itself just renames the sheet with the string(lNum) from th UDF and " ISCOMP".
If i remember correctly the UDF was posted in this forum before and is quite handy