Letter and numeral separation

[º¼ÖÝÈË]

Letter of alphabet and numeral separation
If A1= asd123dsa, how to get the B1= asddsa, C1=123

 

[Bob Phillips]

Easiest way is probably a UDF

Function BreakString(rng As Range, Optional text As Boolean = True)
Dim i As Long
Dim sTemp As String

For i = 1 To Len(rng.Value)
If IsNumeric(Mid(rng.Value, i, 1)) Then
If text Then
'do nothing
Else
sTemp = sTemp & Mid(rng.Value, i, 1)
End If
Else
If text Then
sTemp = sTemp & Mid(rng.Value, i, 1)
End If
End If
Next i
If text Then
BreakString = sTemp
Else
BreakString = Val(sTemp)
End If

End Function

=BreakString(A20) to get the text
=BreakString(A20,False) to get the number

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

if there are always 9 characters with the three numbers in the middle
in B1 enter
=left(A1,3) & right (A1,3)
and in C1 enter
=Mid(A1,4,3)

if the format varies, it gets more complicated.

 

[º¼ÖÝÈË]

Thank, can does use the formula solution?

 

[Don Guillett]

try this

Sub getnumberfromstring()
For Each C In Selection
On Error Resume Next
For i = 1 To Len(C)
If IsNumeric(Mid(C, i, 1)) Then Start = i: Exit For
Next
For i = Len(C) To t Step -1
If IsNumeric(Mid(C, i, 1)) Then stopl = i + 1: Exit For
Next
C.Offset(, 1) = Left(C, Start - 1)
C.Offset(, 2) = Mid(C, Start, stopl - Start)
Next C

 

[Bob Phillips]

Sorry, do you want to ask that again?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

If you absolutel must use Excel standard functions then I believe these will
work for you if your test value is in Cell A1:

Only the text:
Cell B1:
=LEFT(A1,MIN(IF(ISERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},A1)),999,SEARCH({0,1,2,3,4,5,6,7,8,9},A1)))-1)&MID(A1,MAX(IF(ISERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},A1)),0,SEARCH({0,1,2,3,4,5,6,7,8,9},A1)))+1,LEN(A1))

Only the numbers:
Cell C1:
=MID(A1,MIN(IF(ISERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},A1)),999,SEARCH({0,1,2,3,4,5,6,7,8,9},A1))),SUM(IF(ISERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},A1)),0,1)))

(Note: Commit those array formulas by pressing [Ctrl]+[Shift]+[Enter])

I hope that helps.
Ron

 

[Don Guillett]

Nice but cse does not appear to be necessary

 

[Guest]

Absolutely right, Don. I don't know what I was thinking when I typed that.
Good catch. Thanks.

Ron

 

[Harlan Grove]

Easiest way is probably a UDF

Function BreakString(rng As Range, Optional text As Boolean = True)
Dim i As Long
Dim sTemp As String

For i = 1 To Len(rng.Value)
If IsNumeric(Mid(rng.Value, i, 1)) Then
If text Then
'do nothing
Else
sTemp = sTemp & Mid(rng.Value, i, 1)
End If
Else
If text Then
sTemp = sTemp & Mid(rng.Value, i, 1)
End If
End If
Next i
If text Then
BreakString = sTemp
Else
BreakString = Val(sTemp)
End If

End Function

....

The logic could be simplified, no reason to call Mid several times, and
IsNumeric is slower than Like.


Function foo(s As String, Optional n As Boolean) As String
Dim i As Long, k As Long, c As String * 1

k = Len(s)

For i = 1 To k
c = Mid(s, i, 1)
If (c Like "#") = n Then foo = foo & c
Next i

End Function


That said, regular expressions would make this child's play. Using the Subst
udf from

http://groups-beta.google.com/group...programming/msg/1d9ae07c970566de?dmode=source

(or http://makeashorterlink.com/?S512525CA ), the decimal digits would be
given by

=subst(x,"\D+","")

and the other characters by

=subst(x,"\d+","")

 

[Sandy Mann]

Harlan,

Dim i As Long, k As Long, c As String * 1

May I ask why you DIMmed c as String *1?

I always understood that doing maths on strings was meaningless as in
"Sandy" * 1

Regards

Sandy

 

[Bob Phillips]

It is not doing math on it, but declaring it as a one byte string.

Try setting it to say 2 bytes in code and see what it contains after,

c = "AB"

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

I'm not Harlan, but take a look at Dim in VBA's help.

Then look at the description for "type". It contains this line "...String *
length (for fixed-length strings)..."

Then try something like:

Dim myString As String * 1
myString = "asdfasdf"
MsgBox myString

 

[Sandy Mann]

Thank you Dave & Bob,

I did actually look in VBA Help but in my naivety I look up STRING, it
never occurred to me to look up DIM (no puns please <g>)

Regards,

Sandy