Removing leading letters in a text using VBA

[volabos]

Hi,

I am wondering id there is any function in VBA which can replace designated leading letters in a text with nothing.

For example, let say in a cell I have "AAAAA235263", and I want to remove leading "AAAAA" and want to get only "235263".

Any pointer will be very helpful.

[p45cal]

Sure there is.
http://www.vbaexpress.com/forum/showthread.php?70438-Linear-interpolation-in-Excel&p=418024&viewfull=1#post418024

[Aussiebear]

That's a point well made P45cal. However since I'm still suffering the warm gooey feeling of Christmas here's a one last chance Volabos.

Assuming your data in Cells A1: A3, and that you are running a Microsoft system, and also assuming I read the data right off the video here's a starter for you. I can't test this as a Mac can't run CSE entered formulas so here goes;

In Cell B1 write the following formula: =Left(A1,MATCH(TRUE,ISERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0)))-1))

In Cell C1 write the following formula: =Right(A1,(LEN(A1)-((MATCH(TRUE,ISERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0)))+1))

Hopefully this strips the Alphabeticals from the Numerals within the string.

[Aussiebear]

Public Notice: volabos
Yes, there are some suggestions provided by other experts. I can work with these suggestions. pm.

Given that only one solution was provided here, and we don't yet know if either of the two formulas as presented were correct, how can anyone then mark the Thread as solved? I notice also that yet again no acknowledgement of the effort has been offered, despite a very clear warning from others. Let this be a lesson to all people who post here in this forum.... if you haven't got the ethical courage to thank others who try to assist, then please feel free to use another forum. volabos don't let the door hit you on the way out.

[arnelgp]

you can also use RegExp:

Public Function GetNumeric(ByVal var As Variant)
Static oReg As Object
GetNumeric = 0
If IsNull(var) Then
Exit Function
End If
On Error GoTo Create_Object
var = var & ""
With oReg
.Pattern = "([^0-9])"
.Global = True
GetNumeric = Val(.Replace(var, ""))
End With
Exit Function
Create_Object:
Set oReg = CreateObject("VbScript.RegExp")
Resume Next
End Function