Letter and numeral separation

º

º¼ÖÝÈË

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

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)
 
B

bj

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.
 
D

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
 
B

Bob Phillips

Sorry, do you want to ask that again?

--

HTH

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

Ron Coderre

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
 
R

Ron Coderre

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

Ron
 
H

Harlan Grove

Bob Phillips said:
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
Click to expand...
....

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+","")
 
S

Sandy Mann

Harlan,
Dim i As Long, k As Long, c As String * 1
Click to expand...

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
 
B

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)
 
D

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
 
S

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
 
Top