Parsing String w/ Spaces 3

[net123]

I have a field that has a string of characters separated by spaces, for example:

MK RAT CM 0603 NA 17

Is there a way to concatenate it (or take out the spaces so it can look something like this:

MKRATCM0603NA17

I ultimately would like to parse this string and put the parsed strings into different fields within the same row, for example:

Field1 Field2 Field3 ...
MK RAT CM

This is to be done so I can run queries against it.

Does anyone have any good ideas on how to do this?

I was 'thinking' of converting the following psuedocode:

IF LEN(STR) <> 15 THEN ERROR_FIELD
ELSE STR(1,2) --> FIELD1
STR(3,3) --> FIELD2
STR(6,2) --> FIELD3

Please help...

 

[SkipVought]

Hi,

Function StripSpaces(MyString) As String
  For i = 1 to Len(MyString)
    sByte = Mid(MyString, i, 1)
    If sByte <> &quot; &quot; then
      StripSpaces = StripSpaces & sByte
    End If
  Next
End Function

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DaleWatson123321]

You should have specified Excel or Access.

If it's Excel, this will work. This is based on your data being consistent - i.e. same number of characters.

With your data being in column A, enter these formulas:
B1: =LEFT($A1,2)
C1: =MID($A1,4,3)
D1: =MID($A1,8,2)
E1: =MID($A1,11,4)
F1: =MID($A1,16,2)
G1: =MID($A1,19,2)

Then of course copy the formulas down for the number of rows of data in Column A, and convert the formulas to values.

If your application is in Access and you encounter difficulty, you could bring the data into Excel, do the conversion, and then export back to Access.

Hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

Bit of a fudge - this works against text in A1 but can be modded to loop - this parses the text using a space as delimiter:
uCol = 2
mStr = [A1]
For i = 1 To Len(mStr)
If i = Len(mStr) Then Cells(1, uCol).Value = Right(mStr, Len(mStr) - stPos + 1)
If Mid(mStr, i, 1) = &quot; &quot; Then
If uCol = 2 Then
Cells(1, uCol).Value = Left(mStr, i - 1)
uCol = uCol + 1
stPos = i + 1
Else
If i <> Len(mStr) Then
Cells(1, uCol).Value = Mid(mStr, stPos, i - stPos)
uCol = uCol + 1
stPos = i + 1
Else

End If
End If
Else
End If

Next i

OR - you could just use Text To Columns:

Selection.TextToColumns Destination:=Range(&quot;A1&quot, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
TrailingMinusNumbers:=True

Finally, to get rid of spaces, you can just use
=substitute(A1,&quot; &quot;,&quot;&quot


Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[sfvb]

You can use a combination of existing Functions. The Replace function and the Split function:

Dim MyString      As String
Dim MyNewString   As String
Dim MyArray       As Variant

MyString = "MK RAT CM 0603 NA 17"
MyNewString = Replace(MyString, " ", "")
MsgBox MyNewString

MyArray = Split(MyString)
For idx = 0 To UBound(MyArray)
   MsgBox MyArray(idx)
Next

 

[Bowers74]

Whithout wanting to step on anyone's feet here, there is no reason to use VBA in this case. The Excel function SUBSTITUTE() will do the &quot;concatinate&quot; part of what net123 wants to do:

=SUBSTITUTE(A1,&quot; &quot;,&quot;&quot

This will replace all of the spaces with blanks.

Good luck! I am going to take a look at the parsing part later.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

[cough]
last 2 lines of my post Mike
[/cough]

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Bowers74]

Oooops ,

no coffee yet this morning. Sorry!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

no worries

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[WebStar]

check out the Trim function for eliminating spaces from a string

 

[xlbo]

Webstar - leading and trailing spaces - TRIM won't handle spaces INSIDE the string

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244