Find All Numbers and Text in a cell

J

jlclyde

I have a column of Text and numbers that are combined. Sometimes the
column is just numbers and other times it is Numbers and text. For
instance LDDC68, LDDC69..... M45, 46......458, 459. I want to add
the next number in the list. I think I can use isnumber for the ones
that are numbers but not letters. I am doing this through VBA. any
help will be appreciated.

Thanks,
Jay
 
M

Mike H

Hi,

I can understand about a column of cells that contain text and numbers mixed
but having extracted the numbers what do you want to do with them, I don't
understand


I want to add the next number in the list

Please clarify

Mike
 
J

jlclyde

LDDC68, LDDC69.....  M45, M46......458, 459.   I want to add
The next number in the list referes to the next consecutive number
that would happen after the given values. If the value was LDDC69
then the next number would be LDDC70. If the value was M46 the next
number would be M47. If the value were 100 the next value woudl be
101.

Thanks,
Jay
 
J

jlclyde

Here is how I did it with a function. Then I can call this function
any time I want.

Function RemoveText(Cel) As Variant
Dim intIndex As Integer
Dim Str As String

intIndex = 1
Str = ""
Do While intIndex <= Len(Cel) 'Do until all characters have been
checked
Select Case UCase(Mid(Cel, intIndex, 1)) 'How it checks each
character
Case "A" To "Z" 'Checks all non numeric characters'
If intIndex > 1 Then
Str = Str & Mid(Cel, intIndex, 1)
Cel = Left(Cel, intIndex - 1) & Mid(Cel, intIndex + 1)
Else
Str = Str & Mid(Cel, intIndex, 1)
Cel = Mid(Cel, intIndex + 1)

End If
Case Else
intIndex = intIndex + 1
End Select
Loop
RemoveText = Str & Cel + 1
End Function
 
Top