Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Letter and numeral separation
Letter of alphabet and numeral separation
If A1= asd123dsa, how to get the B1= asddsa, C1=123 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
Thank, can does use the formula solution?
|
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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 |
#9
|
|||
|
|||
Absolutely right, Don. I don't know what I was thinking when I typed that.
Good catch. Thanks. Ron |
#10
|
|||
|
|||
"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+","") |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|