Split a value retrieved from a field 2

[369852]

I have a field which contains a value like 'd12345'. I need to separate the character/s from the integers. The character/s will always be first.

Anyone got any ideas?

Thanks in advance :-D

 

[rmikesmith]

369852,

Here is some sample code. Copy into a code module.

Option Explicit

Const TestStr = "abc12345"

Sub Main()
  Parse_Chars (TestStr)
End Sub


Sub Parse_Chars(ByVal InptStr As String)
Dim NoAlpha As Boolean
Dim AlphaStr As String
Dim OneChar As String * 1
Dim NumStr As String
Dim i As Integer

AlphaStr = ""
NumStr = ""
NoAlpha = False
Do
  For i = 1 To Len(InptStr)
    OneChar = Mid$(InptStr, i, 1)
    If OneChar Like "[A-Z,a-z]" Then
      AlphaStr = AlphaStr & OneChar
    Else
      NumStr = Mid$(InptStr, i)
      NoAlpha = True
      Exit For
    End If
  Next i
Loop Until NoAlpha
Debug.Print AlphaStr
Debug.Print NumStr
End Sub

Hope this helps.

M. Smith

 

[DaleWatson123321]

369852,

I've put together a "formula" option specifically for your situation.

It's just a matter of copying the formulas down for each record.

It will place the text in one cell, and the number in the next cell.

If you would like the file, just email me (at my HOME address) and I'll send the file via return email.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[369852]

Thanks a million for your response's m smith & Dale!. Used the code seems to be working great

Thanks again

 

[DaleWatson123321]

Mike,

369852 chose your option, so I consider you "won" and so I'm pleased to also award you with a STAR.

As I'm still making a transition from Lotus 123 code to VBA, my VBA skills still require significant improvement.

It's perhaps for this reason that I've had difficulty in getting your code to work.

I expected to be able to just copy it into a Module as you suggested. However, in doing so, I received &quot;red-lines&quot; for the four lines noted with an arrow ( <=== ) below.

Do
For i = 1 To Len(InptStr) <===
OneChar = Mid$(InptStr, i, 1)
If OneChar Like &quot;[A-Z,a-z]&quot; Then <===
AlphaStr = AlphaStr & OneChar
Else
NumStr = Mid$(InptStr, i)
NoAlpha = True
Exit For <===
End If <===
Next i
Loop Until NoAlpha

Can you please explain what could be causing me to get these red-lines.

Also, can you confirm whether your code is intended to work on just one cell or variable. And, how could it be modified to separate characters from integers in a RANGE of cells.

It appears I mistook 369852's description to mean he wanted to perform the separation on a field containing MULTIPLE records. However, given that others have had situations where they indeed wanted to convert MULTIPLE cells, your help in &quot;modification&quot; of your routine for such a situation would also be appreciated. Thanks !!!

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[macbone2]

The same thing happened to me the other day. Try putting the cursor immediately before the 'If' and backspacing to the beginning of the line. I think occasionally some sort of control characters are picked up on this site when copy/pasting.

Geoff (Macbone2)

 

[rmikesmith]

Dale,

I think Geoff's comment has merit. I do not see anything in the code layout that would cause the redlining. In fact, in this and most cases I copy and paste directly from the VBE to the forum.

As to your other questions. The procedure, as written, accepts a single string as input. It was meant to be primarily a demo of the technique. Not knowing how the output was to used, I simply split the alpha and numeric portions into two local variables, which may or may not be the best way to go for a working procedure; i.e. it might be better to make AlphaStr and NumStr parameters to the Sub, declared byRef. That way, the calling routine could supply whatever variables were appropriate. To work on multiple cells, a calling procedure would iterate through the range, calling Parse_Chars each time. Here's an example

Sub Main()
Dim c As Range
Dim Alpha As String
Dim Num As String

For Each c In Range("A1:A20")
  Parse_Chars c.Text, Alpha, Num
  c.Offset(0,1).Value = Alpha
  c.Offset(0,2).Value = Num
Next c
End Sub


Sub Parse_Chars(byVal InptStr As String, byRef AlphaStr As String, byRef NumStr As String)

Dim NoAlpha As Boolean
Dim OneChar As String * 1
Dim i As Integer

AlphaStr = ""
NumStr = ""
NoAlpha = False
Do
 For i = 1 To Len(InptStr)
   OneChar = Mid$(InptStr, i, 1)
   If OneChar Like "[A-Z,a-z]" Then
     AlphaStr = AlphaStr & OneChar
   Else
     NumStr = Mid$(InptStr, i)
     NoAlpha = True
     Exit For
   End If
 Next i
Loop Until NoAlpha
End Sub

The procedure Main reads the entries in cells A1 through A20, calls Parse_Chars once for each cell, then writes the alpha portion to Column B of the same row and the numeric portion to Column C. Let me know if this helps.

Regards,
Mike