Letter and numeral separation
 

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

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)


"º¼ÖÝÈË" wrote in message
...
Letter of alphabet and numeral separation
If A1= asd123dsa, how to get the B1= asddsa, C1=123

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.

"º¼Ö�ÈË" wrote:

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

Thank, can does use the formula solution?

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
--
Don Guillett
SalesAid Software

"º¼ÖÝÈË" wrote in message
...
Letter of alphabet and numeral separation
If A1= asd123dsa, how to get the B1= asddsa, C1=123

Sorry, do you want to ask that again?

--

HTH

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


"º¼ÖÝÈË" wrote in message
...
Thank, can does use the formula solution?

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,LE N(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

Nice but cse does not appear to be necessary

--
Don Guillett
SalesAid Software

"Ron Coderre" wrote in message
...
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},A
1)),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

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

Ron

"Bob Phillips" wrote...
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/...e?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+","")

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

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk

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)


"Sandy Mann" wrote in message
...
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

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk

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 wrote:

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

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk

--

Dave Peterson

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

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Dave Peterson" wrote in message
...
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 wrote:

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

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk

--

Dave Peterson