function for finding position of numeric character in a string

[Paul]

Hi,

Can anyone help with worksheet function for finding position of numeric
character in a string.

I am trying to extract data into the form 12a345 from data that may be like
any below:

12a345
12a345c
12a345_c
12ab345
12ab345c
12ab345_c

Extracting the first bit is easy with LEFT() but to extract the 345 I need
to find it's position in the string then I can use MID().


--
Regards,

Paul.

[Jim Thomlinson]

Pop this code into your Personal workbook. It is a public function so you
will be able to access via =PERSONAL.XLS!FirstNumber(C5). It finds the firs
number in a string.

HTH

"Paul" wrote:

> Hi,
>
> Can anyone help with worksheet function for finding position of numeric
> character in a string.
>
> I am trying to extract data into the form 12a345 from data that may be like
> any below:
>
> 12a345
> 12a345c
> 12a345_c
> 12ab345
> 12ab345c
> 12ab345_c
>
> Extracting the first bit is easy with LEFT() but to extract the 345 I need
> to find it's position in the string then I can use MID().
>
>
> --
> Regards,
>
> Paul.
>
>
>

[David]

Hi Paul,
This will find out what type of character is in each position. The problem
is that there is multiple "alpha" and multiple "numeric" positions in the
strings you have. I assumed the data was in column 1 and put a formula in B1
to find the character types and then put the results in an array. You can
querry the array for each type. I did not make an attempt to step down the
worksheet, but I don't think that is a substantial problem here. Somehow
IDing the first and last characters of each group of types is the real
problem. I am not even certain at this point exactly what you want extracted,
just the numeric portion of the code? If there is a consistant number of
"numerics" in a row, then I think it is possible to ID the beginning of each
group of numerics and this may come close to what you want.

Sub FindNum()
Dim z() As Variant
ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
TheLength = ActiveCell.Value
ReDim z(TheLength)
Ct1 = 0
CtEr = 0
For Position = 1 To TheLength
ActiveCell.FormulaR1C1 = "=VALUE(MID(RC[-1]," & (Words) & ",1))"
y = TypeName(ActiveCell.Value)
If y = "Error" Then
z(Words) = "Alpha"
Else
z(Words) = "Numeric"
End If
Next Position
Stop
End Sub


"Paul" wrote:

> Hi,
>
> Can anyone help with worksheet function for finding position of numeric
> character in a string.
>
> I am trying to extract data into the form 12a345 from data that may be like
> any below:
>
> 12a345
> 12a345c
> 12a345_c
> 12ab345
> 12ab345c
> 12ab345_c
>
> Extracting the first bit is easy with LEFT() but to extract the 345 I need
> to find it's position in the string then I can use MID().
>
>
> --
> Regards,
>
> Paul.
>
>
>

[David]

Try this for the second group of numerics. Sorry for the first post, changed
some of the code, posed, but had not tested it.

Sub FindNum()
Dim z() As Variant
ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
TheLength = ActiveCell.Value
ReDim z(TheLength)
CtEr = 0
For Position = 1 To TheLength
ActiveCell.FormulaR1C1 = "=VALUE(MID(RC[-1]," & (Position) & ",1))"
y = TypeName(ActiveCell.Value)
If y = "Error" Then
z(Position) = "Alpha"
Else
z(Position) = "Numeric"
Ct1 = Ct1 + 1
If Ct1 = 3 Then SecondNumeric = Position
End If
Next Position
Stop
NumberToPull = Ct1 - 2
ActiveCell.FormulaR1C1 = "=VALUE(MID(RC[-1]," & (Ct1) & "," &
(NumberToPull) & "))"
End Sub

"Paul" wrote:

> Hi,
>
> Can anyone help with worksheet function for finding position of numeric
> character in a string.
>
> I am trying to extract data into the form 12a345 from data that may be like
> any below:
>
> 12a345
> 12a345c
> 12a345_c
> 12ab345
> 12ab345c
> 12ab345_c
>
> Extracting the first bit is easy with LEFT() but to extract the 345 I need
> to find it's position in the string then I can use MID().
>
>
> --
> Regards,
>
> Paul.
>
>
>

[gocush]

Paul,
Try the following code adjusting the location (B2:B10) where your target
cells are as well as the destination should go:


Sub GetSting()
Dim strLeft As String
Dim strMid As String
Dim strRt As String

Dim oCell As Range
Dim Rng As Range
Dim i As Integer
Dim j As Integer

Set Rng = Range("B4:B10")
j = 4
For Each oCell In Rng
strLeft = Left(oCell, 2)
strMid = Mid(oCell, 3, 1)
For i = 1 To Len(oCell) - 3
If IsNumeric(Mid(oCell, j, 1)) Then
strRt = Mid(oCell, j, 3)
oCell.Offset(0, 1) = strLeft & strMid & strRt
GoTo NextCell
Else
j = j + 1
End If
Next i

NextCell:
Next oCell

End Sub

"Paul" wrote:

> Hi,
>
> Can anyone help with worksheet function for finding position of numeric
> character in a string.
>
> I am trying to extract data into the form 12a345 from data that may be like
> any below:
>
> 12a345
> 12a345c
> 12a345_c
> 12ab345
> 12ab345c
> 12ab345_c
>
> Extracting the first bit is easy with LEFT() but to extract the 345 I need
> to find it's position in the string then I can use MID().
>
>
> --
> Regards,
>
> Paul.
>
>
>

[Paul]

Thanks to all.

I will try it ASAP.

[jindon]

Hi
How about UDF?
paste the code onto standatd module
use: if data in cell A1 then
=Paul(A1)
in any cell

Please Login or Register  to view this content.

hope this helps
jindon